SQL Query enhancement

wonder FOOL

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

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.

Chris Meech

I'd do it this way.

select sum(created_count), sum(received_count), sum(closed_count), sum(voided_count) from (
SELECT COUNT(rec.rmaNum) created_count
,0 received_count
,0 closed_count
,0 voided_count
FROM RMARecords rec
LEFT JOIN RMAUsers ucr on ucr.id = rec.userCreated
WHERE rec.rmaStatus = 0
UNION
SELECT 0 created_count
,COUNT(rec.rmaNum) received_count
,0 closed_count
,0 voided_count
FROM RMARecords rec
LEFT JOIN RMAUsers ure on ure.id = rec.userCreated
WHERE (rec.rmaStatus = 1 OR rec.rmaStatus = 2)
UNION
SELECT 0 created_count
,0 received_count
,COUNT(rec.rmaNum) closed_count
,0 voided_count
FROM RMARecords rec
LEFT JOIN RMAUsers ucl on ucl.id = rec.userCreated
WHERE rec.rmaStatus = 3
UNION
SELECT 0 created_count
,0 received_count
,0 closed_count
,COUNT(rec.rmaNum) voided_count
FROM RMARecords rec
LEFT JOIN RMAUsers ucl on ucl.id = rec.userCreated
WHERE rec.rmaStatus = 4
);

You could also look into creating a view for those joins. :)

Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

wonder FOOL

Hello Chris, In your first line

select sum(created_count), sum(received_count), sum(closed_count), sum(voided_count) from (

I am having errors in this line for the column names which you placed inside the brackets. For; created_count, received_count, closed_count, voided_count. The error is; invalid column name. In my sql statement I am only looking to the column name rmaNum.

Chris Meech

Your original post included the 'as' keyword for aliasing. Perhaps your DB requires that. Try the following

...
SELECT COUNT(rec.rmaNum) as created_count
,0 as received_count
,0 as closed_count
,0 as voided_count
FROM RMARecords rec
...

Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

Corporal Agarn

You might try:

SELECT
SUM(CASE WHEN rec.rmaStatus = 0 THEN 1 ELSE 0 END) as Created
SUM(CASE WHEN (rec.rmaStatus = 1 OR rec.rmaStatus = 2) THEN 1 ELSE 0 END) AS Received
SUM(CASE WHEN rec.rmaStatus = 3 THEN 1 ELSE 0 END) AS Closed
SUM(CASE WHEN rec.rmaStatus = 4 THEN 1 ELSE 0 END) AS Voided
FROM RMARecords rec
LEFT JOIN RMAUsers ucr on ucr.id = rec.userCreated

Lost User

TRY THIS

djj55 wrote:

SELECT SUM(CASE WHEN rec.rmaStatus = 0 THEN 1 ELSE 0 END) as Created SUM(CASE WHEN (rec.rmaStatus = 1 OR rec.rmaStatus = 2) THEN 1 ELSE 0 END) AS Received SUM(CASE WHEN rec.rmaStatus = 3 THEN 1 ELSE 0 END) AS Closed SUM(CASE WHEN rec.rmaStatus = 4 THEN 1 ELSE 0 END) AS Voided FROM RMARecords rec LEFT JOIN RMAUsers ucr on ucr.id = rec.userCreated

comma missing..

SELECT
SUM(CASE WHEN rec.rmaStatus = 0 THEN 1 ELSE 0 END) as Created,
SUM(CASE WHEN (rec.rmaStatus = 1 OR rec.rmaStatus = 2) THEN 1 ELSE 0 END) AS Received ,
SUM(CASE WHEN rec.rmaStatus = 3 THEN 1 ELSE 0 END) AS Closed,
SUM(CASE WHEN rec.rmaStatus = 4 THEN 1 ELSE 0 END) AS Voided
FROM RMARecords rec
LEFT JOIN RMAUsers ucr on ucr.id = rec.userCreated

Maulik Dusara Sr. Sofware Engineer I love it when a plan comes together

Corporal Agarn

Thanks! I just did a quick non-tested coding. :-O

Lost User

Hi, It's clear that you have met some trouble in this problem, why not try esProc to solve it? esProc is a tool for mass data computation, especially fit for the complex data computation like yours. Check below for more details: A Query Language Over-perform SQL[^]