Question 21370 in Mysql Question posted in
The official MySQL documentation can be found here.

how to use sql count() function multiple times in same table with where clause – Mysql

Let’s say I have a table:

name number date
name1 91104 ‘2022-12-01’
name2 11161 ‘2022-12-02’

I am writing these queries:

select count(name) from table
    where
        created_at between
            '2022-12-01' and '2022-12-10' and
        terminal_id like '911%'

select count(name) from table
    where
        created_at between
            '2022-12-01' and '2022-12-10' and
        terminal_id like '111%'

How to write query to get this output:

911like 111like
10 25
Tags:

2

Answers


  1. 0

    This is done using count("expression"). This will count when the expression is true

    select count(case when terminal_id like '911%'  
                  then name
               end)  as [911like]
       ,count(case when terminal_id like '111%'  
                  then name
               end)  as [111like]
   from table
 where created_at between '2022-12-01' and '2022-12-10'
    Login or Signup to reply.
  2. 0

    More simplified:

    select sum(terminal_id like '911%') as 911like,
       sum(terminal_id like '111%') as 111like
from my_table
where created_at between '2022-12-01' and '2022-12-10';

    https://dbfiddle.uk/PqdCP0Fq

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search