MySQL - query - multiple group by

Question

I have the following table, in which I'm trying to SUM if type = 'printer', however, I would like not to count repeat 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 | 
+-----------+--------------------------------+

Andomar · Accepted Answer · 2011-02-16 19:47:29Z

8

There are three rows with a type of printer. Sum adds them all up, and returns 3.

If you'd like to see 1 for rows with printers, and 0 otherwise, try max instead of sum:

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

EDIT: To count the number of distinct printers, you could 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

edited Feb 16, 2011 at 19:47

answered Feb 16, 2011 at 19:40

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Imre L Over a year ago

in mysql you dont need max, just omit the function

Andomar Over a year ago

@Josh: You could use a subquery (added to answer) @Imre L: Without max(), it would run the if only for a random row, not all if them

Cheesebaron · Accepted Answer · 2014-01-09 23:54:30Z

8

Use:

   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 don't need to see the rows with zero counts:

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

edited Jan 9, 2014 at 23:54

Cheesebaron

24.5k15 gold badges70 silver badges121 bronze badges

answered Feb 16, 2011 at 19:44

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Comments

jnthnjns · Accepted Answer · 2014-04-09 14:39:48Z

1

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

edited Apr 9, 2014 at 14:39

jnthnjns

8,9254 gold badges44 silver badges70 bronze badges

answered Aug 17, 2012 at 12:48

Arumugam

111 bronze badge

Comments

Aaron Watters · Accepted Answer · 2011-02-16 19:42:01Z

0

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

(I'm guessing: I'm not aquainted with IF(..))

answered Feb 16, 2011 at 19:42

Aaron Watters

2,8464 gold badges24 silver badges37 bronze badges

Collectives™ on Stack Overflow

MySQL - query - multiple group by

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related