Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

grouping values in a group by query

Posted on 2015-02-02
3
Medium Priority
?
174 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 51

Assisted Solution

by:Gustav Brock
Gustav Brock earned 668 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 43

Accepted Solution

by:
pcelba earned 668 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 39

Assisted Solution

by:PatHartman
PatHartman earned 664 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

722 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