** Please solve this group by query...

Venkatesan

13y1.7k

1) My Table:

Query: select mcdesp,mcopsts from machine

Table:

2) My Quries and their outputs:

select mcdesp,count(mcopsts)as TotalMachine from machine group by mcdesp

select mcdesp, count(mcopsts) as GOOD from machine where mcopsts='GOOD' group by mcdesp

select mcdesp,count(mcopsts) as URWT from machine where mcopsts='URWT' group by mcdesp

select mcdesp,count(mcopsts) as URWP from machine where mcopsts='URWP' group by mcdesp

BUT I NEED A RESULT LIKE

Please some one help me..

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sunny Sharma13y ago
Accepted answer
Hi Sharmila,
Use the query below:

SELECT mcdesp, COUNT(mcopsts) AS TotalMachine,
SUM(case when mcopsts='GOOD' then 1 else 0 end) AS 'GOOD',
SUM(case when mcopsts='URWT' then 1 else 0 end) AS 'URWP',
SUM(case when mcopsts='URWT' then 1 else 0 end) AS 'URWP'
FROM machine GROUP BY mcdesp

this will solve the purpose. :)

Don't forget to accept this as answer.

Happy to help !
Venkatesan13y ago
Wow.. Thank you Sunny Kumar..!!
Sunny Sharma13y ago
Hi Venkateshan, sorry for the inconvenience, I've updated the query for SQL Server, previous one was intended for MySQL. Check my previous answer with updated Query. This will help you.

Thanks.
Venkatesan13y ago
While executing that query i'm facing this error.. Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'IF'. Msg 102, Level 15, State 1, Line 2 Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 3 Incorrect syntax near ','. Msg 102, Level 15, State 1, Line 4 Incorrect syntax near ','.
Sunny Sharma13y ago
please share the table/sample table data to get help more quicker.