CODEWITHSUNDEEP
phpmysql
bsandrabr
bsandrabr

Reputation: 143

Can I do this in one Mysql query?

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

Upvotes: 6

Views: 148

Answers (5)

mcliedtk
mcliedtk

Reputation: 1031

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

Upvotes: 0

DVK
DVK

Reputation: 129433

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

Upvotes: 2

symcbean
symcbean

Reputation: 48357

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.

Upvotes: 1

Jan Tojnar
Jan Tojnar

Reputation: 5524

SELECT COUNT(*) FROM table WHERE columnA=1 or columnB=1

Upvotes: -1

Joel
Joel

Reputation: 3060

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.

Upvotes: -1

Related Questions