Using COUNT()

[lurtnowski]

I have

SELECT COUNT(unknown_id_a) AS total1,COUNT(unknown_id_b) AS total2 FROM data_connections WHERE unknown_id_a = 1 OR unknown_id_b = 1;

on this table

the result

why is the value of total1 and total2 the same?
only unknown_id_ b is 1 so shouldne only total2 be 1 and total1 be 0?

[DaveMaxwell]

@lurtnowski

why is the value of total1 and total2 the same?
only unknown_id_ b is 1 so shouldne only total2 be 1 and total1 be 0?

Because you’re counting total rows returned, not comparing values. So if unknown_a OR unknown_b are = 1, you’re going to get an instance of the field.

You’re going to need something like…

SELECT SUM(IF(unknown_id_a = 1, 1, 0)) total1
     , SUM(IF(unknown_id_b = 1, 1, 0)) total2
  FROM data_connections 
 WHERE unknown_id_a = 1 
    OR unknown_id_b = 1;

What this does is looks at the value of each field and sets a value of 1 if the field has the value being looked for, then sums up the ones that match the value being searched for.

[lurtnowski]

ve never used an if in a query
What are the 3 numbers in it?

[benanamen]

MySQL IF() function - w3resource

[Thallius]

To make it more confusing, to be more compatible with standard SQL query you could use CASE instead of IF, which syntax is very special for MySQL while CASE is similar over most SQL dialects.

SELECT SUM(CASE WHEN unknown_id_a = 1 THEN 1 ELSE 0 END) AS total1,
               SUM(CASE WHEN unknown_id_b = 1 THEN 1 ELSE 0 END) AS total2
FROM data_connections 
WHERE unknown_id_a = 1 
              OR unknown_id_b = 1;

[r937]

@Thallius

To make it more confusing,

yes, CASE!! boo, IF!!

and to make it slightly less confusing, don’t use a hack like SUM() when what you’re actually doing is counting – there’s a better function for that, and it’s called COUNT()

SELECT COUNT(CASE WHEN unknown_id_a = 1 
                  THEN 'humpty' END) AS total1
     , COUNT(CASE WHEN unknown_id_b = 1 
                  THEN 'dumpty' END) AS total2
  FROM data_connections 
 WHERE 1 IN ( unknown_id_a , unknown_id_b)