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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I must have been asleep at the switch!
ASKER
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!!!!!