SQL Server Performance

Selecting 1 row by different where's

Discussion in 'SQL Server 2005 General Developer Questions' started by mlanio, Sep 25, 2006.

  1. mlanio New Member

    I've a got query that is using a group by with sums. I need to select a column twice to display different data but a where only would do the same data. In essence:

    Col 1 Col 2 Hours Committed hours
    100 Item 100 flag A total flag b total

    So for item 100, it would sum up hours from a detail table where flag = a AND sum up hours where flag = b. The hours in the detail table is the same column, just selecting different rows to give me a sum in the committed hours than in the Hours column.

    I'm really not sure how to go about this. Any ideas?
  2. Chappy New Member

    join the table to itself, and give each one an alias

    Im assuming Item is the Primary Key. try something like...

    select
    a.item, sum(ISNULL(a.hours, 0)), sum(ISNULL(b.hours, 0))
    from
    mytable a
    full outer join mytable b on (a.item = b.item)
    where
    a.flag = 'a' and
    b.flag = 'b'
    group by
    a.item


Share This Page