Post reply

SQL 2000 Grouping Query

  • October 9, 2008 at 10:16 am

    #194473

    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

    #883037

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 9, 2008 at 12:19 pm

    #883076

    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

    #883087

    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

    #883101

    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