Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 210
  • Last Modified:

Group by clause not returning expected results

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
StacyD
Asked:
StacyD
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
StacyDAuthor Commented:
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
 
StacyDAuthor Commented:
I must have been asleep at the switch!
0

Featured Post

Independent Software Vendors: 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!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now