CODEWITHSUNDEEP
mysql
liontass
liontass

Reputation: 740

Multiple Counts in a single query SQL

I have wrote this SQL code to count in a row different values in months.

"SELECT 
            (SELECT COUNT(id) MONTH(date) AS inMonth, YEAR(date) AS inYear  FROM ptiseis WHERE katigoria='prosarmogi') low,
            (SELECT COUNT(id) MONTH(date) AS inMonth, YEAR(date) AS inYear  FROM ptiseis WHERE  katigoria='organa') moderate,
            (SELECT COUNT(id) MONTH(date) AS inMonth, YEAR(date) AS inYear  FROM ptiseis WHERE  katigoria='nautilia') high,

          GROUP BY MONTH(date), YEAR(date)
          ORDER BY inYear, inMonth  ";

But it doesn't work.What I have wrong and how I can fix it?

Upvotes: 2

Views: 151

Answers (1)

forpas
forpas

Reputation: 164089

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.

Upvotes: 2

Related Questions