MySQL - query - several groups of

Question

I have the following table in which I am trying to use SUM if type = 'printer', however I would not want to count the repetition of client_ids. So I expect something like this:

+------+-----------+-----------+ | k_id | client_id | type | +------+-----------+-----------+ | 1 | 100 | pc | | 2 | 101 | printer | | 3 | 101 | printer | | 4 | 101 | printer | | 5 | 102 | cellphone | +------+-----------+-----------+ 

Query:

  SELECT client_id, SUM(IF(type = 'printer', 1,0)) FROM FOO GROUP BY type, client_id; 

Result:

 +-----------+--------------------------------+ | client_id | SUM(IF(type = 'printer', 1,0)) | +-----------+--------------------------------+ | 102 | 0 | | 100 | 0 | | 101 | 3 | +-----------+--------------------------------+ 

Expected Result:

 +-----------+--------------------------------+ | client_id | SUM(IF(type = 'printer', 1,0)) | +-----------+--------------------------------+ | 102 | 0 | | 100 | 0 | | 101 | 1 | +-----------+--------------------------------+ 

Answer 1

There are three lines of type printer . Sum adds them all and returns 3.

If you want to see 1 for lines with printers and 0 otherwise, try max instead of Sum :

 MAX(IF(type = 'printer', 1,0)) ^^^ 

EDIT: to count the number of individual printers, you can use a subquery:

 SELECT client_id , ( select count(*) from FOO as f2 where f1.client_id = f2.client_id and type = 'Printer' ) FROM FOO as f1 GROUP BY client_id 

Answer 2

Using:

  SELECT x.client_id, COUNT(DISTINCT y.type) FROM FOO x LEFT JOIN FOO y ON y.client_id = x.client_id AND y.type = 'printer' GROUP BY x.client 

If you do not need to see lines with a zero number:

  SELECT client_id, COUNT(DISTINCT type) FROM FOO WHERE type = 'printer' GROUP BY type, client_id; 

Answer 3

 SELECT client_id, if( `type` = 'printer', 1, 0 ) FROM foo GROUP BY TYPE , client_id 

Answer 4

 SELECT distinct client_id, (IF(type = 'printer', 1,0)) FROM FOO 

(I guess: I am not familiar with IF (..))