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 4 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply