Click here to Skip to main content
15,884,425 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
1) My Table:

Query:
SQL
select mcdesp,mcopsts from machine


Table:
mcdesp	mcopsts
A	GOOD
A	GOOD
B	GOOD
C	URWP
C	GOOD
A	URWT
A	URWT


2) My Queries and their outputs:


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


mcdesp	TotalMahine
A	4
B	1
C	2


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


SQL
mcdesp	GOOD
A	2
B	1
C	1


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


mcdesp	URWT
A	2
B	
C	


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


mcdesp	URWP
A	
B	
C	1



BUT I NEED A RESULT LIKE

mcdesp	TotalMachine	GOOD	URWP	URWT
A	4	2	0	2
B	1	1	0	0
C	2	1	1	0


Please some one help me..
Posted
Updated 22-May-13 23:42pm
v2
Comments
skydger 23-May-13 5:46am    
Consider to use pivot operator, if your SQL Server version is 2005 or higher

Another solution is to use Pivot[^]:

SQL
SELECT mcdesp, [GOOD], [URWP], [URWT]
FROM (
    SELECT mcdesp,mcopsts
    FROM machine) AS DT
PIVOT (COUNT(mcopsts) FOR mcdesp IN([GOOD], [URWP], [URWT])) AS PT


If you would like to use dynamic pivots, see these:
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/87582c2b-2f4a-4be3-a1a7-3bc8235d4c7f/[^]
http://sqlwithmanoj.wordpress.com/2011/01/25/dynamic-pivot/[^]

To get total:
SQL
SELECT mcdesp, [GOOD], [URWP], [URWT], [GOOD] + [URWP] + [URWT] AS Total
FROM(
    SELECT mcdesp, [GOOD], [URWP], [URWT]
    FROM (
        SELECT mcdesp,mcopsts
        FROM machine) AS DT
    PIVOT (COUNT(mcopsts) FOR mcdesp IN([GOOD], [URWP], [URWT])) AS PT) AS T
 
Share this answer
 
v2
Comments
VIP Venkatesan 23-May-13 8:29am    
when i execute the ABOVE QUERY i'm facing an error like

'Incorrect syntax near 'PIVOT''.
You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.

i was executed this in sql server 2005
Maciej Los 23-May-13 8:52am    
Which query, with Total column?
Try:
SQL
SELECT mcdesp,
   COUNT(mcdesp) AS TotalMachine, 
   SUM(CASE WHEN mcopsts='GOOD' THEN 1 ELSE 0 END) AS GOOD,
   SUM(CASE WHEN mcopsts='URWP' THEN 1 ELSE 0 END) AS URWP,
   SUM(CASE WHEN mcopsts='URWT' THEN 1 ELSE 0 END) AS URWT
FROM myTable GROUP BY mcdesp
 
Share this answer
 
Comments
VIP Venkatesan 25-May-13 2:29am    
SELECT mcdesp as MachineName,
COUNT(mcdesp) AS TotalMachine,
SUM(CASE WHEN mcopsts='GOOD' THEN 1 ELSE 0 END) AS GOOD,
SUM(CASE WHEN mcopsts='URWP' THEN 1 ELSE 0 END) AS URWP,
SUM(CASE WHEN mcopsts='URWT' THEN 1 ELSE 0 END) AS URWT,
sum(RequiredMachines) as RequiredMachines
FROM machine GROUP BY mcdesp

and i need a difference between totalmachines and requiredmachines.

can u say the query for this?
OriginalGriff 25-May-13 3:49am    
How would I know what RequiredMachines is? It isn't in your data as shown!
VIP Venkatesan 25-May-13 4:04am    
Hi Sir, Let my table be

mcdesp mcopsts RequiredMachie
A GOOD 5
A GOOD 4
B GOOD 3
C URWP 1
C GOOD 6
A URWT 4
A URWT 1
OriginalGriff 25-May-13 4:22am    
Have you tried returning the calculation?
SUM(RequiredMachines) - COUNT(mcdesp) AS Diff
VIP Venkatesan 25-May-13 4:26am    
thank you sir.. it is working.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900