Menu
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
210
asked Aug 12 '14 22:08
SUM() and COUNT() functions SUM of values of a field or column of a SQL table, generated using SQL SUM() function can be stored in a variable or temporary column referred as alias. The same approach can be used with SQL COUNT() function too.
If you need to add a group of numbers in your table you can use the SUM function in SQL. This is the basic syntax: SELECT SUM(column_name) FROM table_name; If you need to arrange the data into groups, then you can use the GROUP BY clause.
SUM() and MAX() at the same time Notice that all aggregate functions except COUNT(*) ignore the NULL Rating for the ID=5 row. COUNT(*) counts rows, whereas COUNT(col) counts non-null values. So to answer your question, just go ahead and use SUM() and MAX() in the same query.
No, we can't use a MAX(COUNT(*) and we can not layer aggregate functions on top of one another in the same SELECT clause.
SELECT
COUNT(X),
SUM(CASE WHEN Z = 1 THEN X ELSE 0 END)
FROM tbl WHERE X>Y
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
