MySQL: Calculate number using COUNT within the SELECT statement?

Question

I want to basically take a number (total_score) and divide it by the number of (release_date)s that are <= today. I definitely want to do the math within the SELECT statement. I tried this but it doesn't seem to work:

SELECT 
total_score / COUNT(release_date <= CURDATE()) as final_score
from movies
WHERE id = 12

So say the total score is 400 and the number of release_dates that are <= today is 2. final_score should come out as 200.

Can I calculate within the SELECT statement like this? COUNT(release_date <= CURDATE())

Erm...I see what you are doing. You will need a subquery.

crush
– crush

2013-03-04 14:02:35 +00:00
Commented Mar 4, 2013 at 14:02 — crush
– crush, Commented Mar 4, 2013 at 14:02

Obl Tobl · Accepted Answer · 2013-03-04 14:03:13Z

1

Maybe try this one:

SELECT 
total_score / (SELECT COUNT(release_date) FROM movies where release_date <= CURDATE()) as final_score
from movies
WHERE id = 12

answered Mar 4, 2013 at 14:03

Obl Tobl

5,6828 gold badges44 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Obl Tobl Over a year ago

no problem, if it's an useful answer then maybe you can accept it as right answer to help other users.

SteveP · Accepted Answer · 2013-03-04 14:07:44Z

0

One reason that doesn't work is you are only selecting one row (where id=12). You need to select from the table twice. Once to get the total_score for id 12, and another time to count the number of rows before CURDATE. Try something like:

select A.total_score / count(B.release_date) from movies as A, movies as B
   where A.id=12 and B.release_date <= CURDATE();

answered Mar 4, 2013 at 14:07

SteveP

19.2k9 gold badges49 silver badges62 bronze badges

Comments

Kickstart · Accepted Answer · 2013-03-04 14:16:09Z

0

Maybe try something like this:-

SELECT (total_score / score_count) as final_score
FROM movies
CROSS JOIN (SELECT COUNT(*) as score_count
FROM movies
WHERE release_date <= CURDATE()) AS Sub1
WHERE id = 12

edited Mar 4, 2013 at 14:16

answered Mar 4, 2013 at 14:03

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

Comments

HILARUDEEN S ALLAUDEEN · Accepted Answer · 2013-03-04 14:23:32Z

0

As per my assumption , Id is primary key(i.e Movie's ID). Then the query return always single row. Now to final_score is depends on release date. That release date must be previous to current date, so following query is enough to achieve you goal,

SELECT total_score / DATEDIFF(CURDATE(), release_date) AS final_score FROM movies WHERE id = 12

answered Mar 4, 2013 at 14:23

HILARUDEEN S ALLAUDEEN

1,7621 gold badge20 silver badges34 bronze badges

Collectives™ on Stack Overflow

MySQL: Calculate number using COUNT within the SELECT statement?

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related