Back

Explore Courses Blog Tutorials Interview Questions
0 votes
1 view
in SQL by (6.1k points)

I'm building a chart and I need to receive the data for every month.

Following is my first request which is working:

SELECT s.GSP_nom AS nom, timestamp, AVG( v.vote +  v.prix  ) /2 AS avg
FROM votes_serveur AS v
INNER JOIN serveur AS s ON v.idServ = s.idServ
WHERE s.valide =1
AND v.date > CURDATE() -30
GROUP BY s.GSP_nom
ORDER BY avg DESC

However, in my case I need to write 12 requests to receive the data for the 12 previous months, is there any method to avoid writing:

//  example for the previous month
 AND v.date > CURDATE() -60
AND v.date < CURDATE () -30

I heard about the INTERVAL, I went through the MySQL doc though I can't able to implement it.

Any example of using the INTERVAL, please?

1 Answer

0 votes
by (12.7k points)

You need DATE_ADD/DATE_SUB:

AND v.date > (DATE_SUB(CURDATE(), INTERVAL 2 MONTH))
AND v.date < (DATE_SUB(CURDATE(), INTERVAL 1 MONTH))

This will do the magic for you. 

Want to be a SQL expert? Join the Intellipaat's SQL Training program to learn more.

Related questions

0 votes
1 answer
asked Jul 15, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
asked Jul 19, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
asked Jul 19, 2019 in SQL by Tech4ever (20.3k points)
Welcome to Intellipaat Community. Get your technical queries answered by top developers!

28.4k questions

29.7k answers

500 comments

94k users

Browse Categories

...