I have this mysql table:
DATE | VALUE
and I wish to become a select which shows me this information as:
DATE | COUNT TOTAL | COUNT VAL=1 | COUNT VAL=2
Any ideas how I can achieve this?
-
This sounds like a homework assignment. You should add that tag, and then post what you've tried so far, explain what didn't work as you expected, and then we can try and help you figure out why. We're not going to do the work for you, because you won't learn anything that way.Ken White– Ken White2011-09-09 20:50:20 +00:00Commented Sep 9, 2011 at 20:50
-
Well I can create seperate queries (one for a total overview, one for VAL1 and VAL2). But I can't get them combined: SELECT date, COUNT(*) FROM payments WHERE paymentProviderId = 2 GROUP BY YEAR( DATE ) , MONTH(DATE ) , DAY(DATE ) (and no it's not a homework assignment)user485659– user4856592011-09-09 20:50:38 +00:00Commented Sep 9, 2011 at 20:50
-
I have this exact question right now, and it is not homework in my case.reinierpost– reinierpost2018-01-31 12:04:25 +00:00Commented Jan 31, 2018 at 12:04
Add a comment
|
3 Answers
I think with SUM() you can get neater code. Since it sums the values respective expression for row.
SELECT date,
COUNT(*),
SUM( value = 1 ),
SUM( value = 2 )
FROM my_table
Official Documentation can be found here.
1 Comment
SG 86
Thats the more cleaner way i guess
SELECT `date`, COUNT(*) AS `COUNT TOTAL`,
COUNT(CASE `value` WHEN 1 THEN `value` END) AS `COUNT VAL=1`
COUNT(CASE `value` WHEN 2 THEN `value` END) AS `COUNT VAL=2`
FROM mytable
GROUP BY `date`
The CASE expressions will be null when there is no match. Nulls are not counted by COUNT().
I imagine you might want a dynamic number of columns, one column for each value found in the data. This is not possible in SQL. The columns must be known at the time you write the query.
So you have two options to get subtotals per value:
First query the distinct values from all rows of
valueand construct an SQL query dynamically, appending one column in the select-list for each distinct value you want to report. Then run this SQL query.Alternatively, fetch all the rows as rows. Count the subtotals per value in application code.
One further alternative is to count subtotals by groups, and include totals:
SELECT `value`, `date, COUNT(*) AS `COUNT SUBTOTAL`
FROM mytable
GROUP BY `value`, `date` WITH ROLLUP
But that doesn't report the subtotals in columns as you requested, it reports the subtotals in rows.
