CODEWITHSUNDEEP
mysql
SilentCry
SilentCry

Reputation: 2092

Select average and average by condition

In my query, I need to select four values - average, average for certain date, total count, and count for certain date. My query is now only for average and count. Is it needed to have two queries or is it possible to use only one?

SELECT AVG(value) as average, 
COUNT(*) as noRates 
FROM `ratings` WHERE mid = 31

What I wish is something like

SELECT AVG(value) as average, 
COUNT(*) as noRates, 
AVG(value WHERE datecolumn = '2014-10-17') as averageToday, 
COUNT(* WHERE datecolumn = '2014-10-17') as noRatesToday 
FROM `ratings` 
WHERE mid = 31

Upvotes: 1

Views: 1939

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

For conditional average you can use case and for conditional count you can directly put your expression in sum to behave it like as count function

SELECT 
AVG(`value`) AS average, 
COUNT(*) AS noRates,
 AVG( CASE WHEN datecolumn = '2014-10-17' THEN `value` ELSE 0 END) AS averageToday,
 SUM(datecolumn = '2014-10-17') AS noRatesToday 
 FROM `ratings` 
 WHERE `mid` = 31

Upvotes: 2

Related Questions