Solved

Group by clause not returning expected results

Posted on 2008-10-28
3
195 Views
Last Modified: 2010-04-21
Hi Guys:
I am trying to aggregate results based on employee, product, office & revenue. When I use this statement


SELECT office,  sum(revenue)
FROM [Fiscal 2008] AS f
WHERE f.Date=808
and office = 660
and fa = 224
and [product code] = 1
group by office, revenue

I get these results.office      Expr1001
660      55
660      85.11
660      94.04
660      117.77
660      269.36

 I would expect to see 660 621.28

When I run this:

SELECT  sum(revenue)
FROM [Fiscal 2008] AS f
WHERE f.Date=808
and office = 660
and fa = 224
and [product code] = 1

I do see this
621.28

But as soon as I start adding in group by fields:

SELECT trim(f.empid), trim(f.Office), trim(f.[Product Code]), sum(revenue)
FROM [Fiscal 2008] AS f
WHERE f.Date=808
and office = 660
and fa = 224
and [product code] = 1
GROUP BY  trim(f.empid), trim(f.Office), trim(f.[Product Code]), revenue

I get this.
Expr1000      Expr1001      Expr1002      Expr1003
083MR      660      1      55
083MR      660      1      85.11
083MR      660      1      94.04
083MR      660      1      117.77
083MR      660      1      269.36

I want to see one line for this emp/office/product and see the rolled up revenue {I trimmed the fields thinking maybe the DB had some spaces in those values}.

Any ideas?
0
Comment
Question by:StacyD
  • 2
3 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 125 total points
Comment Utility
first query: if you want to SUM() by a field, do not GROUP BY  that field:
SELECT office,  sum(revenue)
FROM [Fiscal 2008] AS f
WHERE f.Date=808
and office = 660
and fa = 224
and [product code] = 1
group by office

Open in new window

0
 

Author Comment

by:StacyD
Comment Utility
I'm such a dope!
SELECT trim(f.empid), trim(f.Office), trim(f.[Product Code]), sum(revenue)
FROM [Fiscal 2008] AS f
WHERE f.Date=808
and office = 660
and fa = 224
and [product code] = 1
GROUP BY  trim(f.empid), trim(f.Office), trim(f.[Product Code])


Thank you - works fine!!!!!
0
 

Author Closing Comment

by:StacyD
Comment Utility
I must have been asleep at the switch!
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 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

17 Experts available now in Live!

Get 1:1 Help Now