CODEWITHSUNDEEP
sqlreporting
webworm
webworm

Reputation: 11019

Transform multiple queries into single row

I have a report that I would like to base on a single SQL statement. The problem is the data is based on several SQL statements. For example.

SELECT COUNT(*) as 'Cases Opened' 
FROM tblCases 
WHERE DateAssigned BETWEEN @StartDate AND @EndDate

SELECT COUNT(*) as 'Cases Closed' 
FROM tblCases 
WHERE ClosedDate BETWEEN @StartDate AND @EndDate

SELECT COUNT(*) as 'Tickets Issued' 
FROM tblTicket 
WHERE DateIssued BETWEEN @StartDate AND @EndDate

SELECT COUNT(*) as 'Warnings Issued' 
FROM tblWarning 
WHERE DateIssued BETWEEN @StartDate AND @EndDate

Is there a way to turn these four seperate SQL statements into a single SQL statement such that each result is listed as a column? For example ..

Cases Opened        Cases Closed       Tickets Issued        Warnings Issued
******************************************************************************
   256         |      165          |        56           |          165

EDIT I am using SQL Server and no there is no relationship between the tables.

Upvotes: 2

Views: 4408

Answers (3)

bigtang
bigtang

Reputation: 271

Check out the pivot statement if you are running in SQLServer

Upvotes: 1

Dan J
Dan J

Reputation: 16708

You could union and pivot the data, like so:

SELECT SUM(CASE WHEN FieldName='Cases Opened' THEN Value ELSE 0 END) AS Cases_Opened,
       SUM(CASE WHEN FieldName='Cases Closed' THEN Value ELSE 0 END AS Cases_Closed,
       SUM(CASE WHEN FieldName='Warning Issued' THEN Value ELSE 0 END) AS Warnings_Issued,
       SUM(CASE WHEN FieldName='Tickets Issued' THEN Value ELSE 0 END) AS Tickets_Issued
FROM 
(
SELECT COUNT(*) as Value, 'Cases Opened' as FieldName
FROM tblCases 
WHERE DateAssigned BETWEEN @StartDate AND @EndDate

UNION

SELECT COUNT(*) as Value, 'Cases Closed' as FieldName
FROM tblCases 
WHERE ClosedDate BETWEEN @StartDate AND @EndDate

UNION

SELECT COUNT(*) as Value, 'Tickets Issued' as FieldName
FROM tblTicket 
WHERE DateIssued BETWEEN @StartDate AND @EndDate

UNION

SELECT COUNT(*) as Value, 'Warnings Issued' as FieldName
FROM tblWarning 
WHERE DateIssued BETWEEN @StartDate AND @EndDate
)

Upvotes: 1

Nathan Feger
Nathan Feger

Reputation: 19496

select 
 (
 SELECT COUNT(*)  
 FROM tblCases 
 WHERE DateAssigned BETWEEN @StartDate AND @EndDate
 ) as 'Cases Opened' ,
 (SELECT COUNT(*)  
 FROM tblCases 
 WHERE ClosedDate BETWEEN @StartDate AND @EndDate
 ) as 'Cases Closed' ,
 (SELECT COUNT(*)  
 FROM tblTicket 
 WHERE DateIssued BETWEEN @StartDate AND @EndDate
 ) as 'Tickets Issued' ,
 (SELECT COUNT(*) 
 FROM tblWarning 
 WHERE DateIssued BETWEEN @StartDate AND @EndDate
 )  as 'Warnings Issued'
from dual

from dual would be necessary in oracle, mysql supports it but is not necessary and I'm not sure about sqlserver since I don't have one in front of me.

Upvotes: 7

Related Questions