SQL query enhancement

Question

0.00/5 (No votes)

See more:

I wrote a query that gets the counts of which has different statuses.
The query goes like

SQL

SELECT COUNT(rec.rmaNum) as Created
FROM RMARecords rec
LEFT JOIN RMAUsers ucr on ucr.id = rec.userCreated
WHERE rec.rmaStatus = 0 
UNION 
SELECT COUNT(rec.rmaNum) as Received
FROM RMARecords rec
LEFT JOIN RMAUsers ure on ure.id = rec.userCreated
WHERE (rec.rmaStatus = 1 OR rec.rmaStatus = 2)
UNION
SELECT COUNT(rec.rmaNum) as Closed
FROM RMARecords rec
LEFT JOIN RMAUsers ucl on ucl.id = rec.userCreated
WHERE rec.rmaStatus = 3
UNION
SELECT COUNT(rec.rmaNum) as Voided
FROM RMARecords rec
LEFT JOIN RMAUsers ucl on ucl.id = rec.userCreated
WHERE rec.rmaStatus = 4

The output of this query is;

Created
-------
0
1
3
6

I think because of the union the result set comes like this. What I want as output is different

Created Received Closed Voided
--------/----------/-------/--------
---0---/----1-----/--3---/---6----

How can I get as a result set like the upper result? Thanks in advance.

Posted 1-Mar-12 8:43am

wonder-FOOL

Add a Solution

2 solutions

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)

Jyothikarthik_N · Answer 1 · 2012-03-01T10:13:00

Solution 1

Check this,

http://weblogs.asp.net/salimfayad/archive/2008/01/30/rows-to-columns.aspx[^]

It should help

*Mark as answer if this solves

Posted 1-Mar-12 10:13am

Jyothikarthik_N

graciax8 · Answer 2 · 2012-03-01T15:50:00

try this one wonder-FOOL.

SQL

SELECT (SUM(CASE WHEN rec.rmaNum = 0 THEN 1 ELSE 0 END)) as 'Created',
(SUM(CASE WHEN rec.rmaNum IN ('1','2') THEN 1 ELSE 0 END)) as 'Received',
(SUM(CASE WHEN rec.rmaNum = 3 THEN 1 ELSE 0 END)) as 'Closed',
(SUM(CASE WHEN rec.rmaNum = 4 THEN 1 ELSE 0 END)) as 'Voided',
FROM RMARecords AS rec
LEFT OUTER JOIN RMAUsers AS usr ON rec.userCreated = usr.id
GROUP BY rec.rmaNum

If you have questions just comment, and if this is helpful mark as answer.

Best Regards,
@iamsupergrasya