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 !

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.

So what have you tried for this query so far?

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

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)

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.

if that's true, then you did not explain yout situation correctly

here is the test using exactly the same data that you provided

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 .......?

case when manager is null then 1 else 0 end