Solved

grouping values in a group by query

Posted on 2015-02-02
3
160 Views
Last Modified: 2015-02-05
I have a table and am doing a "totals" query in query design, using 2 fields "number" and "cost", I want to group on number, and sum the cost.

However, rather than getting an itemised list of sums per each unique number, I want to group all numbers (but exclude one number from the count, perhaps this could be put in the where clause) and get a sum for all numbers in one total figure, and do a total sum for it. Any pointers if this can be done?
0
Comment
Question by:pma111
3 Comments
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 167 total points
ID: 40583730
You can do the first grouping and summing in a normal query, then use this as source for a form you view in datasheetview. Here you can set a bottom line to show the second level of grouping, a Total on all rows of the query.

/gustav
0
 
LVL 41

Accepted Solution

by:
pcelba earned 167 total points
ID: 40583737
I would recommend to switch the query designer into SQL view and everything is in your hands then :-)

All numbers summed except one (you don't need GROUP BY):

SELECT SUM(cost) AS costSUM
  FROM YourTable
WHERE number <> 'Excluded number'

The WHERE clause expression must follow the number column data type rules so if the column is numeric then simply write:

WHERE number <> 555

The result grouped by number:

SELECT number, SUM(cost) AS costSUM
  FROM YourTable
WHERE number <> 'Excluded number'
GROUP BY number

And now you may display above queries in the Query designer.
0
 
LVL 35

Assisted Solution

by:PatHartman
PatHartman earned 166 total points
ID: 40584056
You can't have two levels of grouping in the same query if that is what you are asking for.  To do that, you would need separate queries to group at each level.  Then a union query to bring it all together.  However, in most cases, it simply doesn't make sense to do this.  Do one level of grouping in the query and use a report to do the second level.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question