开发者

MySQL - query - multiple group by

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;
开发者_StackOverflow

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 | 
+-----------+--------------------------------+


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


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;


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


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

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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜