Solved

grouping values in a group by query

Posted on 2015-02-02
3
162 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 42

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 36

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

820 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