1

Consider I have a below table name "temp"

   id   | name
   -----+-------
      1 | AAA        
      2 | BBB        
      3 | BBB

Am getting the count of the names with below query

  SELECT name , count(*) FROm temp where name IN ('AAA','BBB','CCC') group by 1

When I execute the query I got the below output

      name | count 
------------+-------
AAA        |     1
BBB        |     2

But I need the below output

    name | count 
    ------+-------
   AAA   |     1
   BBB    |     2
   CCC   |     0

How to get this output???

Bhuvanesh
  • 1,269
  • 1
  • 15
  • 25

3 Answers3

0

You can create another temporary table tmp2 with values 'AAA', 'BBB', 'CCC'... and then your selection query would be something like this:

SELECT tmp2.name, count(temp.name) 
FROM tmp2 
LEFT JOIN temp ON temp.name=tmp2.name
GROUP BY tmp2.name
daniherculano
  • 373
  • 1
  • 9
  • 26
  • 1
    Thank you for your answer.. With your answer I have made some changes and got the output. – Bhuvanesh Sep 15 '16 at 11:33
  • Please beware that MySQL is a multi-user environment. Whatever you insert into `tmp2` will be shared by all users, unless you properly define it as temporary table. – Álvaro González Sep 16 '16 at 08:17
0

If you want a pure SQL single-query cross-DBMS solution you need something on this line (untested code):

SELECT names.name, count(*) AS count
FROM (
    SELECT 'AAA' AS name
    UNION ALL SELECT 'BBB'
    UNION ALL SELECT 'CCC'
) names
LEFT JOIN temp ON names.name=temp.name
GROUP BY names.name

This involves dynamic SQL so you need to either:

  • Write a complex procedure to generate it.
  • Use your client language (Java, PHP, whatever...). In this case you could simply take the easy path: keep your current query and take care of empty values in your display code.

If you don't mind having several queries, another alternative would be a temporary table to replace the names ad-hoc subquery. This feature is normally not cross-DBMS but MySQL and PostgreSQL seem to share the basic syntax:

CREATE TEMPORARY TABLE names (...)

If you even need to support a DBMS where temporary tables behave too differently, you could always emulate them with a regular table and an extra session_id column to isolate data from different users.

Álvaro González
  • 142,137
  • 41
  • 261
  • 360
0

You need a left join between the actual values you want in your output (although this could be a SELECT DISTINCT from another table where all the values reside if available), this can be generated using SELECT and UNION and your query. I often us this for BI where the 'presentation layer' is less well defined in some cases.

See an example below:

CREATE TABLE temp (id INTEGER, `name` CHAR(3));

#ALTER TABLE temp CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO temp VALUES (1, 'AAA'), (2, 'BBB'), (3, 'BBB');

SELECT A.`name`, IFNULL(B.nameCOUNT, 0) nameCOUNT FROM
    (
    SELECT 'AAA' `name` UNION ALL 
    SELECT 'BBB' `name` UNION ALL
    SELECT 'CCC' `name`
    ) A
LEFT OUTER JOIN
    (
    SELECT `name`, COUNT(*) nameCOUNT FROM temp GROUP BY `name`
    ) B
ON A.`name` = B.`name`;

Note the commented collation statement, not sure if this will be necessary on your setup, uncomment if you see an error like this:

ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '='

Let me know if you have any questions, regards,

James

James Scott
  • 1,032
  • 1
  • 10
  • 17