CODEWITHSUNDEEP
sqlsqlite
user1925772
user1925772

Reputation: 107

SQL unify COUNT and SUM in one query

I want to combine two SELECTs into one single query, as the example below:

TABLE tbl

╔════╦════╦════╦═══╗
║ id ║ X  ║ Y  ║ Z ║
╠════╬════╬════╬═══╣
║  0 ║  1 ║  2 ║ 0 ║
║  1 ║  3 ║  0 ║ 1 ║
║  2 ║  5 ║  6 ║ 1 ║
║  3 ║  7 ║  8 ║ 0 ║
║  4 ║  9 ║  4 ║ 1 ║
║  5 ║ 11 ║ 10 ║ 0 ║
╚════╩════╩════╩═══╝

SELECT COUNT(X) FROM tbl WHERE X>Y
SELECT SUM(X) FROM tbl WHERE X>Y AND Z=1

the first SELECT returns 3 and the second 12. I want to combine the two selects in a single query to get the result

╔══════════╦════════╗
║ COUNT(X) ║ SUM(X) ║
╠══════════╬════════╣
║        3 ║    12  ║
╚══════════╩════════╝

I am using SQLite3

Upvotes: 6

Views: 184

Answers (1)

FuzzyTree
FuzzyTree

Reputation: 32392

SELECT 
    COUNT(X),
    SUM(CASE WHEN Z = 1 THEN X ELSE 0 END) 
FROM tbl WHERE X>Y

Upvotes: 15

Related Questions