SQL QUERY Using Group by..

Question

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 22-May-13 23:11pm

VIP Venkatesan

Updated 22-May-13 23:42pm

Maciej Los

v2

Add a Solution

Comments

skydger 23-May-13 5:46am

Consider to use pivot operator, if your SQL Server version is 2005 or higher

2 solutions

Solution 2

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

Posted 23-May-13 0:52am

Maciej Los

Updated 23-May-13 1:17am

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?

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Accepted Answer · 2013-05-22T23:43:00

Solution 1

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

Posted 22-May-13 23:43pm

OriginalGriff

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.