Group by and multiple where clauses on the same table
-
Hi,
I am pretty new to sql but need to pull some information off of our DB. Basically I need to get the count for several different sets of where queries off of one table that are grouped by a certain field.
SELECT COUNT(*) AS Expr1, USERFROM TableA
WHERE (NOT (STATUS= '90R')) AND (NOT (STATUS= '99I'))
GROUP BY USER
SELECT COUNT(*) AS Expr1, USER
FROM TableA
WHERE (CONVERT(varchar(10), COMPLETEDATE, 112) = CONVERT(varchar(10), GETDATE(), 112))
GROUP BY USER
SELECT COUNT(*) AS Expr1, USER
FROM TableA
WHERE (STATUS= '20A')
GROUP BY USER
SELECT COUNT(*) AS Expr1, USER
FROM TableB
WHERE (LEFT (STARTTIME, 11) = LEFT (GETDATE(), 11))
GROUP BY USER
Now all of these different select clauses work fine on their own, but I would prefer them all in one view as opposed to creating 4 different views and joining them in one table. Essentially I want one column with every different user possible down the rows and with the count for the different where clauses in other columns. So it would be as follows:
USER 1 2 3 4
a 13 2 4 44
b 2 0 1 12
c 6 4 3 8
d 9 6 6 35
I have tried several different ways to get this working and it is currently getting the better of me! If anyone has any suggestions or advice on how to do this it would be greatly appreciated!
-
See if this helps
SELECT USER,SUM(CASE WHEN (NOT (STATUS= '90R')) AND (NOT (STATUS= '99I')) THEN 1 ELSE 0 END) AS Col1,
SUM(CASE WHEN (CONVERT(varchar(10), COMPLETEDATE, 112) = CONVERT(varchar(10), GETDATE(), 112)) THEN 1 ELSE 0 END) AS Col2,
SUM(CASE WHEN (STATUS= '20A') THEN 1 ELSE 0 END) AS Col3
FROM TableA
GROUP BY USER
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Here's something that may get you started. I make no guarantees as to how well it will perform.
SELECT USER,SUM(CASE WHEN STATUS NOT IN ('90R','99I') THEN 1 ELSE 0 END) AS CountOfFirstCriteria,
SUM(CASE WHEN dateadd(dd, datediff(dd,0, COMPLETEDATE),0) = dateadd(dd, datediff(dd,0, getdate()),0) THEN 1 ELSE 0 END AS CountOfSecondCriteria
FROM TableA
GROUP BY USER
I changed the date comparison as trimming off the time with dateadd and datediff is faster than doing to with conversion to varchars.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
You need to use a case expression, as in:
SELECT USER
,SUM(CASE WHEN (NOT (STATUS= '90R')) AND (NOT (STATUS= '99I')) THEN 1 ELSE 0 END) AS StatusCount
,SUM(CASE WHEN CompletedDate >= DATEADD(day, DATEDIFF(day, 0, getdate())) THEN 1 ELSE 0 END) AS CompletedToday
,SUM(CASE WHEN Status = '20A' THEN 1 ELSE 0 END) AS Status20A
,SUM(CASE WHEN StartTime >= DATEADD(day, DATEDIFF(day, 0, getdate())) THEN 1 ELSE 0 END) AS StartedToday
FROM TableA
GROUP BY USER
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.How to post questions to get better answers faster
Managing Transaction Logs -
I think I had the same problem. Look at:
http://qa.sqlservercentral.com/Forums/Topic655885-1292-1.aspx#bm659745
Select
ALIAS1.naam,
ALIAS1.Aantal_Incidenten,
ALIAS2.Aantal_Inc_Portal
FROM
(
select vestiging.naam, count(*) as Aantal_Incidenten
from incident, vestiging
where incident.aanmeldervestigingid = vestiging.unid
group by vestiging.naam
) ALIAS1 INNER JOIN
(
select vestiging.naam, count(*) as Aantal_Inc_Portal
from incident, vestiging
where incident.aanmeldervestigingid = vestiging.unid and soortbinnenkomstid='0k0001'
group by vestiging.naam
)ALIAS2 ON ALIAS1.naam = ALIAS2.naam
(
select ......
)ALIAS3 on ALIAS1.naam = ALIAS3.naam
What you basicly do, is joining the together throug aliases.
ALIAS2.naam, ALIAS3.naam are joined by ALIAS1.naam so no need to repeat them in the main select item.
-
Thankyou very much for the help guys. The advice was spot on and I have now got it sorted.
Mark, thanks for that link, I will try and make sure any future posts have all the required information!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply