CODEWITHSUNDEEP
mysqlsqlcount
Aman Kumar
Aman Kumar

Reputation: 480

Multiple COUNTs in MYSQL with condition on individual column

I have a table mentioned below and I'm trying to get total count based on condition with field cont and group by entry

someTable

    ID    guid      entry    cont
    ---   -----     -----    -----
    1     g1        e1       NULL
    2     g2        e2       NULL
    3     g1        e1       NULL
    4     g3        e1       72345
    5     g1        e3       NULL
    6     g2        e2       34567

The desired Result is

    entry    total_c   total_con
    ---      -----      ----------
    e1       3          1
    e2       2          1
    e3       1          0

I am using the following query, but it returns total_con of whole table for each row

SELECT entry, count(*) AS total_c, (SELECT count(*) FROM someTable WHERE cont IS NOT NULL) AS total_con FROM someTable GROUP BY entry

which is giving

    entry    total_c   total_con
    ---      -----      ----------
    e1       3          2
    e2       2          2
    e3       1          2

How should i achieve the result? Any help would be appreciated

Thanks in advance

Upvotes: 0

Views: 39

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

You would just use COUNT(). It does exactly what you want:

SELECT entry, COUNT(*) AS total_c, 
       COUNT(cont) AS total_con 
FROM someTable 
GROUP BY entry;

COUNT() counts the number of non-NULL values.

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

Use conditional statement for the third column :

select entry, count(*) as total_c, 
       sum(case when cont is not null then 1 else 0 end) as total_con
  from someTable
 group by entry

or replace total_con column with sum(sign(ifnull(cont ,1))) as an alternative.

Demo

Upvotes: 1

forpas
forpas

Reputation: 164089

You need conditional aggregation:

SELECT 
  entry, 
  count(*) AS total_c, 
  sum(cont is not null) AS total_con 
FROM someTable 
GROUP BY entry

The expression cont is not null evaluates to 0 or 1 so SUM() sums over these values.
See the demo.
Results:

| entry | total_c | total_con |
| ----- | ------- | --------- |
| e1    | 3       | 1         |
| e2    | 2       | 1         |
| e3    | 1       | 0         |

Upvotes: 1

Related Questions