October 9, 2008 at 10:16 am
The following query will return two rows of data for each employee that has billable and non billable items. I need the data all in one row for each employee. If I take the billable out of the grouping I get an error because billable is not an aggregate function and needs to be in the grouping. Is there a way I can get this query to return me one row of data for each employee listing the billable, non billable and total?
select employee_id,
employee_name,
'transactions-NB' = (select count(trans_id) where billable = 'N'),
'Copies-NB' = (select sum(meter_end - meter_start - meter_wasted) where billable = 'N'),
'Amount-NB' = (select sum(total_price) where billable = 'N'),
'transactions-B' = (select count(trans_id) where billable = 'Y'),
'Copies-B' = (select sum(meter_end - meter_start - meter_wasted) where billable = 'Y'),
'Amount-B' = (select sum(total_price) where billable = 'Y'),
'transactions-ALL' = count(trans_id),
'Copies-All' = sum(meter_end - meter_start - meter_wasted),
'Amount-All' = sum(total_price)
from master_trans
group by employee_id, employee_name, billable
October 9, 2008 at 11:00 am
Can you please post the table structure (as a create table statement) and some sample data (as insert statements).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 9, 2008 at 12:19 pm
You are going about this the hard way. You want to use "SUM( CASE ... )" constructs like this:
select employee_id,
employee_name,
SUM( CASE When billable = 'N' Then 1 Else 0 End ) as [transactions-NB],
SUM( CASE When billable = 'N'
Then (meter_end - meter_start - meter_wasted) Else 0 End ) as [Copies-NB],
SUM( CASE When billable = 'N' Then (total_price) Else 0 End) as [Amount-NB],
SUM( CASE When billable = 'Y' Then 1 Else 0 End ) as [transactions-B],
SUM( CASE When billable = 'Y'
Then (meter_end - meter_start - meter_wasted) Else 0 End ) as [Copies-B],
SUM( CASE When billable = 'Y' Then (total_price) Else 0 End) as [Amount-B],
COUNT(trans_id) as [transactions-All],
SUM(meter_end - meter_start - meter_wasted) as [Copies-All],
SUM(total_price) as [Amount-All],
from master_trans
group by employee_id, employee_name
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 9, 2008 at 12:31 pm
Thank you for your help. This worked like a gem, and it makes sense to me now. Gail, thanks for your reply too.
Have a good day.
October 9, 2008 at 12:58 pm
Glad we could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply