I have a table with two columns:
column A column B
1 2
1 2
2 1
I want to return total of ones = 3 total of twos = 3
The best I can come up with is two queries like so:
SELECT sum(CASE WHEN columnA =1 THEN 1 ELSE 0 END )
+ sum(CASE WHEN columnB =1 THEN 1 ELSE 0 END )
SELECT sum(CASE WHEN columnA =2 THEN 1 ELSE 0 END )
+ sum(CASE WHEN columnB =2 THEN 1 ELSE 0 END )
Can this be done in one query? Thanks
SELECT SUM(IF(columnA=1, 1, 0) + IF(columnB=1, 1, 0)) as ones,
SUM(IF(columnA=2, 1, 0) + IF(columnB=2, 1, 0)) as twos
FROM myTable;
C.
In general, you would count things like so:
SELECT columnA, COUNT(*) FROM myTable
GROUP BY columnA
to get the count of all different values in columnA.
To get everything in one query, I would try something like this.
SELECT Result.Val, COUNT(Result.Val) AS Count
FROM (
SELECT ColumnA AS Val
FROM TableName
UNION
SELECT ColumnB AS Val
FROM TableName
) AS Result
GROUP BY Result.Val
You didn't specify if you want to do this as 2 rows or as 2 values in a row.
Two rows are somewhat obvious (just union together all the values from each columns, and count(1) group by value against the result of the union; so I'll assume you want to do one row.
If you only have 1s or 2s, it's simple:
SELECT SUM(A+B-2) 'twos', SUM(4-A-B) 'ones' FROM myTable
SELECT COUNT(*) FROM table WHERE columnA=1 or columnB=1