Multiple Counts Within a Single 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.

Multiple counts within a single SQL query

@VoteyDisciple's answer is on the right track, but his query needs some improvements:

SELECT c.id, c.title,
    SUM(ts1.section_id = 1) AS doc1,
    SUM(ts1.section_id = 2) AS doc2,
    SUM(ts1.section_id = 3) AS doc3,
    SUM(ts1.section_id = 4) AS doc4
FROM category AS c
  LEFT JOIN category_link_section AS ts1
    ON (c.id = ts1.category_id)
GROUP BY c.id;

---

Explanations:

• The IF() expressions are redundant because equality already returns 1 or 0.
• Take the ts1.section_id=1 out of the join condition, or you'll never get the other section_id values.
• Group by c.id only. I assume the OP only wants one row per category, and columns for counts of each section_id value for the respective category. If the query grouped by c.id, ts1.section_id, then there'd be up to four rows per category.
• Move the commas in the select-list. Commas floating at the start of the line look ugly. ;-)

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

Any way to have multiple counts within same SQL query?

The canonical method uses case with an aggregation function:

select sum(case when condition1 then 1 else 0 end),
       sum(case when condition2 then 1 else 0 end)

The SQL Standard method uses filter:

select count(*) filter (where condition1),
       count(*) filter (where condition2)

multiple count conditions with single query

If you want to get number of students who got A in History in one column, number of students who got B in Maths in second column and number of students who got E in Geography in third then:

select
    sum(case when [History] = 'A' then 1 else 0 end) as HistoryA,
    sum(case when [Maths] = 'B' then 1 else 0 end) as MathsB,
    sum(case when [Geography] = 'E' then 1 else 0 end) as GeographyC
from Table1

If you want to count students who got A in history, B in maths and E in Geography:

select count(*)
from Table1
where [History] = 'A' and [Maths] = 'B' and [Geography] = 'E'

Select multiple count(*) in multiple tables with single query

A more traditional approach is to use "derived tables" (subqueries) so that the counts are performed before joins multiply the rows. Using left joins allows for all id's in basic to be returned by the query even if there are no related rows in either joined tables.

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

Multiple COUNT() for multiple conditions in one query (MySQL)

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

---

Related Topics