Link to home
Start Free TrialLog in
Avatar of StacyD
StacyD

asked on

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?
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of StacyD
StacyD

ASKER

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!!!!!
Avatar of StacyD

ASKER

I must have been asleep at the switch!