January 22, 2007 at 5:09 am
I run the ff 2 queries regularly:
SELECT STATUS, COUNT(STATUS) AS Expr1
FROM dbo.DOCUMENTS
WHERE (COPIES = 0)
GROUP BY STATUS
ORDER BY STATUS
and
SELECT STATUS, COUNT(STATUS) AS Expr1
FROM dbo.DOCUMENTS
WHERE (COPIES = 1)
GROUP BY STATUS
ORDER BY STATUS
Both queries work fine. Is there a way that I can merge the above queries into one query?
January 22, 2007 at 5:36 am
You can use UNION to combine two queries - ie :-
SELECT STATUS, COUNT(STATUS) AS Expr1
FROM dbo.DOCUMENTS
WHERE (COPIES = 0)
GROUP BY STATUS
union all
SELECT STATUS, COUNT(STATUS) AS Expr1
FROM dbo.DOCUMENTS
WHERE (COPIES = 1)
GROUP BY STATUS
ORDER BY STATUS
note - only one ordered by...
this will give you two rows in your resultset - one for copies =0 and one for copies = 1
January 23, 2007 at 1:22 am
Or for two columns and one row. Untested, but should work.
SELECT Status, SUM(CASE Copies WHEN 0 THEN 1 ELSE 0 END) AS CountCopies0, SUM(CASE Copies WHEN 1 THEN 1 ELSE 0 END) AS CountCopies1
FROM dbo.Documents
GROUP BY Status
ORDER bY Status
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply