合并mysql中id的计数总和

Question

我不是mysql专家，我需要帮助来进行计数查询，我需要合并计数从id 5和11到一个计数数= 286的总和，并在这种情况下给出平台名称为GCS。

   SELECT DISTINCT (p.id) AS id, (p.name) AS platform,
IFNULL(count(e.id), 0) AS count
FROM event e, lu_platform p
WHERE e.platform_id = p.id
AND p.id NOT IN ( 10, 15, 17, 18 )
AND e.sourcetype_id = 1
AND e.event_datetime BETWEEN '2013-11-4'
AND '2013-11-10' AND e.sender_id  NOT IN ( 759, 73 )
GROUP BY p.id ORDER BY id;

+----+---------------------------+-------+
| id | platform                  | count |
+----+---------------------------+-------+
|  3 | GGG                       |   414 |
|  4 | KIKI                      |   156 |
|  5 | KJC                       |   284 |
|  6 | LOLO                      |     4 |
|  7 | MOD                       |  1147 |
|  8 | MARKT                     |  1049 |
| 11 | GCS                       |     2 |
| 12 | POLAR                     |    30 |
| 14 | GUAE                      |   145 |
+----+---------------------------+-------+

Answer 1

尝试使用sub-query

SELECT SUM(count) as count, `GCG` as platform
FROM
(
    SELECT DISTINCT (p.id) AS id, (p.name) AS platform,
    IFNULL(count(e.id), 0) AS count
    FROM event e, lu_platform p
    WHERE e.platform_id = p.id
    AND p.id NOT IN ( 10, 15, 17, 18 )
    AND e.sourcetype_id = 1
    AND e.event_datetime BETWEEN '2013-11-4'
    AND '2013-11-10' AND e.sender_id  NOT IN ( 759, 73 )
    GROUP BY p.id ORDER BY id;
) T
WHERE id IN (5,11)

Answer 2

一种可能的方法是使用子查询、sum

和IF函数，或者case表达式CASE WHEN THEN：

SELECT sum( case when id in ( 5, 11 ) then count else 0 end ) as count_5_11,
       sum( if( id in ( 3, 4 ), count, 0 ) ) As count_3_4
FROM (
  -- your query goes here
  SELECT DISTINCT (p.id) AS id, (p.name) AS platform,
         IFNULL(count(e.id), 0) AS count
  ....
  ....
  ....
  ....
  ....
) AS some_alias

Answer 3

感谢您的回复。在上面的两个答案中，我只得到一个结果，而不是列表。我认为最好的方法是像sarwar026提到的那样，在应用程序中这样做。

我还注意到ISNULL(count(e.id)，0)没有像我预期的那样工作，没有记录的平台没有返回0，它们被跳过。