How to Get Multiple Counts With One SQL Query

How to get multiple counts with one SQL query?

You can use a CASE statement with an aggregate function. This is basically the same thing as a PIVOT function in some RDBMS:

SELECT distributor_id,
    count(*) AS total,
    sum(case when level = 'exec' then 1 else 0 end) AS ExecCount,
    sum(case when level = 'personal' then 1 else 0 end) AS PersonalCount
FROM yourtable
GROUP BY distributor_id

Multiple Counts in a single query SQL

I think that this is what you are trying to do:

SELECT 
  YEAR(date) AS inYear,
  MONTH(date) AS inMonth,
  SUM(katigoria='prosarmogi') low,
  SUM(katigoria='organa') moderate,
  SUM(katigoria='nautilia') high
FROM ptiseis
GROUP BY inYear, inMonth
ORDER BY inYear, inMonth;

You want to count the number of rows for each of the 3 cases grouped my year, month.

Select multiple counts from one database table in one sql command access

It looks like this may be an issue caused by not using table aliases. Because you are doing sub-queries on the same table that the outer SELECT is using and not giving the outer table an alias, both of the conditions in the WHERE of the sub-query are only using data in the sub-query.

In other words, when you write:

SELECT COUNT(*) FROM mytable WHERE mytable.survey_answer = 'low' and state = mytable.state

It doesn't know anything about the outer query.

Try this:

SELECT t1.STATE,
  (SELECT COUNT(*) FROM mytable t2 WHERE t2.state = t1.state AND t2.survey_answer = 'low') low,
  (SELECT COUNT(*) FROM mytable t3 WHERE t3.state = t1.state AND t3.survey_answer = 'moderate') moderate,
  (SELECT COUNT(*) FROM mytable t4 WHERE t4.state = t1.state AND t4.survey_answer = 'high') high,
FROM mytable t1
GROUP BY t1.state

SQL SELECT multiple count in one query

You should be able to generate the wanted counts in a single table query:

select 
       redeemed_code
     , count(*) as tot_uses
     , count(distinct redeemed_user) as tot_users
from redeemed
group by redeemed_code

You can join this to the coupon table for final output, for example with a left join you would get all coupons listed in coupon even if none have been redeemed yet.

 select
      c.coupon_id
    , c.coupon_code
    , coalesce(d.tot_uses,0) as tot_uses
    , coalesce(d.tot_users,0) as tot_users
from coupon as c
left join (
         select 
                 redeemed_code
              ,  count(*) as tot_uses
              , count(distinct redeemed_user) as tot_users
         from redeemed
         group by redeemed_code
         ) as d on c.coupon_code = d.redeemed_code

select
      basic.id
    , coalesce(a.LinkACount,0) LinkACount
    , coalesce(b.linkBCount,0) linkBCount
from basic 
left join (
     select id, Count(linkA_ID) LinkACount from LinkA group by id
     ) as a on a.id=basic.id
left join (
     select id, Count(linkB_ID) LinkBCount from LinkB group by id
     ) as b on b.id=basic.id

SELECT color, COUNT(*) FROM t_table GROUP BY color

SELECT keyid, name, section, address, updatedAt,
      (SELECT COUNT(*)
       FROM store s
       WHERE l.keyid = s.keyid
      ) AS storeCount
FROM library l;