Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2006
    Posts
    5

    Unanswered: Complex sum and case statement(for me!)

    I have 1 table
    TABLE 1
    software os osversion manager
    office windows 2000 man1
    apache solaris 5.6 man2
    adobe solaris 5.8 man1
    data2 someOS 2.4 man3

    .....and goes on
    Objective :
    Is to list distinct software,os,osversion and count for each manager.
    I want to get this table

    software os osversion man1 man2 man3
    apache solaris 5.6 10 0 0
    apache solaris 5.8 1 4 0
    adobe solaris 5.8 0 1 0 ...etc

    Thanks !
    Last edited by mudyo30; 11-30-06 at 12:24.

  2. #2
    Join Date
    Nov 2006
    Posts
    5

    Anyone ?

    I am putting here the problem again . Fields are seperate by ,
    TABLE
    Software,Version,Manager
    software1, 1.1,Manager1
    software 2,2.1,Manager 1
    software 3,2.0 ,Manager 3
    software 1,1.0,Manager 2
    software1,1.1,Manager1

    And the result should be

    Software,Version ,Manager1,Manager2,Manager3
    software1,1.1, 2 , 0, 0
    software2,2.1,1,0,0
    software3,2.0,0,0,1

    Basically group by software,version and count for every manager.

  3. #3
    Join Date
    Mar 2004
    Posts
    480
    So what have you tried for this query so far?

  4. #4
    Join Date
    Nov 2006
    Posts
    5
    select software,version,sum(case manager when 'Manager1' then 1 else 0 end) as Manager1, sum(case manager when 'Manager2' then 1 else 0 end) as Manager2, sum(case manager when 'Manager3' then 1 else 0 end) as Manager3 from table GROUP BY software,version;

    This kind of query screws up the number if software name is same .

    Another query that I tried -
    select software,version,count(distinct hostname) as manager1 where manager='manager1' from table GROUP BY software,version;

    This WORKS ...but then the output is only for one manager.
    software, version, manager1
    software1, 1.1, 2
    software2, 2.0, 3
    etc...etc..etc..


    I want
    Software,Version ,Manager1,Manager2,Manager3
    software1,1.1, 2 , 0, 0
    software2,2.1,1,0,0
    software3,2.0,0,0,1

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mudyo30
    select software,version,sum(case manager when 'Manager1' then 1 else 0 end) as Manager1, sum(case manager when 'Manager2' then 1 else 0 end) as Manager2, sum(case manager when 'Manager3' then 1 else 0 end) as Manager3 from table GROUP BY software,version;

    This kind of query screws up the number if software name is same
    please explain why

    because that query looks correct

    p.s. please do not cross-post the same question into other forums (the other thread has been removed)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2006
    Posts
    5
    If I use -
    select software,version,sum(case manager when 'Manager1' then 1 else 0 end) as Manager1, sum(case manager when 'Manager2' then 1 else 0 end) as Manager2, sum(case manager when 'Manager3' then 1 else 0 end) as Manager3 from table GROUP BY software,version;

    Sample Output -
    Software | Version | Manager1 | Manager2 |....etc.
    +------------+---------------+-------+------+
    | Software1 | 1.0 | 204 | 0 |
    | Software1 | 2.0 | 2940 | 228 |
    | Software1 | 3.0 | 3060 | 1104 |

    This is incorrect data.

    If I use -
    select software,version,count(distinct hostname) as manager1 from table where manager='manager1' GROUP BY software,version;

    Output is -
    | Software | Version | manager1 |
    +------------+-------------+--------------------------+
    | Software1 | 1.0 | 34 |
    | Software1 | 2.0 | 490 |
    | Software1 | 3.0 | 510 |

    These are correct numbers.But then I don't have manager2 and 3 numbers here.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mudyo30
    This is incorrect data.
    if that's true, then you did not explain yout situation correctly

    here is the test using exactly the same data that you provided
    Code:
    create table mudyo30
    ( software  varchar(13)
    , version  varchar(13)
    , manager  varchar(13)
    );
    insert into mudyo30 values
     ( 'software1','1.1','Manager1' )
    ,( 'software2','2.1','Manager1' )
    ,( 'software3','2.0','Manager3' )
    ,( 'software1','1.0','Manager2' )
    ,( 'software1','1.1','Manager1' )
    ;
    select software
         , version
         , sum(case manager when 'Manager1'
                            then 1 else 0 end) as Manager1
         , sum(case manager when 'Manager2'
                            then 1 else 0 end) as Manager2
         , sum(case manager when 'Manager3'
                            then 1 else 0 end) as Manager3
      from mudyo30
    group
        by software
         , version
    
    software  version  Manager1  Manager2  Manager3
    software1   1.0        0         1         0
    software1   1.1        2         0         0
    software2   2.1        1         0         0
    software3   2.0        0         0         1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2006
    Posts
    5
    Thank you so much for puttint it like this.

    What was happening was probably my master table had bad data, it had several other rows. I was able to fix it eventually by selecting right data from the table in a view.

    One more question I had was , if the manager column has the value of 'NULL' how can I use it in sum statement ?
    will it be ..

    ......,sum(case manager when NULL then 1 else 0 end ) as NULL .......?

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    case when manager is null then 1 else 0 end
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •