CODEWITHSUNDEEP
t-sqlsql-server-2014
Addeladde
Addeladde

Reputation: 787

T-SQL Two counts with different where clause

I want to count values on a table with different where clauses and wondering if there is a better way of doing it.

In this code i count one value.

SELECT v.name, count(v.value) AS v1
FROM dbo.table as v
WHERE v.organisationID = 2
AND v.datecreated > '2018-01-01'
AND v.datecreated < '2018-05-01'
AND v.value = 1
GROUP BY v.name

I also want to count all rows with value = 2 my way of doing it is like this with a sub select.

SELECT v.name, count(v.value) AS v1, (SELECT v2.name, count(v2.value)  
FROM dbo.table as v2
WHERE v2.organisationID = 2
AND v2.datecreated > '2018-01-01'
AND v2.datecreated < '2018-05-01'
AND v2.value = 2
GROUP BY v2.name) AS v2
FROM dbo.table as v
WHERE v.organisationID = 2
AND v.datecreated > '2018-01-01'
AND v.datecreated < '2018-05-01'
AND v.value = 1
GROUP BY v.name

The table contains > 100 millions rows so I really want the fastest way. I use clustered columnstore index on the table.

Is there some way of doing it whitout sub-select.

Pseudo code:

SELECT v.name, count(v.value where v.value=1) AS v1, count(v.value where v.value=2) AS v2
FROM dbo.table as v
WHERE v.organisationID = 2
AND v.datecreated > '2018-01-01'
AND v.datecreated < '2018-05-01'
GROUP BY v.name

Upvotes: 5

Views: 83

Answers (1)

Lamak
Lamak

Reputation: 70638

Yes, just use a CASE expression:

SELECT  v.name, 
        SUM(CASE WHEN v.value = 1 THEN 1 ELSE 0 END) AS v1,
        SUM(CASE WHEN v.value = 2 THEN 1 ELSE 0 END) AS v2
FROM dbo.table as v
WHERE v.organisationID = 2
AND v.datecreated > '2018-01-01'
AND v.datecreated < '2018-05-01'
AND v.value IN (1,2)
GROUP BY v.name
;

Upvotes: 3

Related Questions