SQL Server Count data with 2 conditions on same query

3qpi33ja 于 2023-03-11 发布在其他

关注(0)|答案(3)|浏览(181)

Here is my sample table:

owner        file_type       status
John            doc_1           1  
John            doc_2           1   
John            doc_3           0
Mike            doc_1           1
Mike            doc_2           0
Mike            doc_3           1   
Dona            doc_1           0   
Dona            doc_2           0
Dona            doc_3           0  
Jane            doc_1           1   
Jane            doc_2           1
Jane            doc_3           1

Result I need to see from my query is this:

owner        uploaded       remaining
John            2               1  
Mike            2               1  
Dona            0               3   
Jane            3               0

Here is the code I tried.

SELECT owner AS 'Name', COUNT(file_type) AS 'uploaded'
FROM sampleTable
WHERE [status] = 1
GROUP BY owner

Based on my code I get the count of uploaded documents,but how do I also get the remaining count in the same query the other condition is [status] = 0 ?

sql-server

来源：https://stackoverflow.com/questions/75675319/count-data-with-2-conditions-on-same-query

3条答案

按热度按时间

qojgxg4l1#

We can use conditional aggregation here:

SELECT
    owner AS Name,
    COUNT(CASE WHEN status = 1 THEN 1 END) AS uploaded,
    COUNT(CASE WHEN status = 0 THEN 1 END) AS remaining
FROM sampleTable
GROUP BY owner

Each of the above CASE expressions conditionally counts the number of times the status be 0 or 1.

赞(0）回复(0）举报 2023-03-11

dauxcl2d2#

Just use a CASE expression:

SELECT  owner [Name], 
        SUM(CASE WHEN file_type = 1 THEN 1 ELSE 0 END) uploaded,
        SUM(CASE WHEN file_type = 0 THEN 1 ELSE 0 END) remaining
FROM sampleTable 
GROUP BY owner;

赞(0）回复(0）举报 2023-03-11

vs3odd8k3#

Assuming that status is always 0 or 1 , I would recommend arithmetics rather than case expressions. I would assume this solution to be a little more efficient than the case approach (and it also neater):

SELECT owner AS name, SUM(status) uploaded, SUM(1 - status) remaining
FROM sampleTable
GROUP BY owner

赞(0）回复(0）举报 2023-03-11

我来回答

SQL Server Count data with 2 conditions on same query

3条答案

相关问题

热门标签

最新问答