Solved

grouping values in a group by query

Posted on 2015-02-02
3
155 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 34

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Read about achieving the basic levels of HRIS security in the workplace.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

759 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now