CODEWITHSUNDEEP
mysqlsqljoinsum
teddytash
teddytash

Reputation: 21

SQL Totals for year and also set period

I have the following SQL database: tablename

date        fname   surname     points  display
2015-08-08  John    Lennon      5       1
2015-08-16  Ringo   Starr       2       1
2015-08-24  Paul    McCartney   3       1
2015-10-07  George  Harrison    1       1
2015-10-15  John    Lennon      3       1
2015-10-23  Ringo   Starr       5       1
2015-12-07  George  Harrison    1       1
2015-12-14  Ringo   Starr       5       1
2015-12-22  George  Harrison    3       1
2016-02-03  Paul    McCartney   4       1
2016-02-13  John    Lennon      5       1
2016-02-20  Paul    McCartney   1       1
2016-04-04  Ringo   Starr       2       1
2016-04-09  George  Harrison    2       1
2016-04-20  John    Lennon      5       1

At present I just use this database to pull the total points for the current year where display = '1' and use the following code:

SELECT id, fname, surname, points, CONCAT(surname,forename) 'fullname', SUM(points) AS total_points
FROM tablename 
WHERE date >= '2016-01-01' AND display ='1' 
GROUP BY fname, surname
ORDER BY total_points DESC, surname ASC, forename ASC

However I would like to add an extra piece of data to the result, giving me the points over the past 15 days. I can pull this on its own by using:

SELECT id, fname, surname, points, CONCAT(surname,forename) 'fullname', SUM(points) AS fifteenday_points
FROM tablename 
WHERE date >= '2016-04-08' AND display ='1' 
GROUP BY fname, surname
ORDER BY total_points DESC, surname ASC, forename ASC

So, how do I go about this? Do I need to use some sort of join? If so, where do I even begin due to the differing WHERE statements? Or is there a simple addition I can make to the first query which will allow me to return the fifteenday_points data?

Upvotes: 0

Views: 46

Answers (2)

teddytash
teddytash

Reputation: 21

SELECT tablename.fname, tablename.surname, CONCAT(tablename.surname,tablename.fname) AS 'fullname',  SUM(points) AS total_points, fifteenday_points
FROM tablename JOIN (
SELECT tablename.fname, tablename.surname, SUM(points) AS fifteenday_points
FROM tablename 
WHERE enddate >= NOW() - INTERVAL 15 DAY  AND display ='1' 
GROUP BY tablename.surname, tablename.fname) AS fifteen_days
ON tablename.surname = fifteen_days.surname AND tablename.fname = fifteen_days.fname
WHERE YEAR(enddate) = YEAR(NOW()) AND display ='1' 
GROUP BY tablename.surname, tablename.fname
ORDER BY total_points DESC, tablename.surname ASC, tablename.fname ASC;

Thanks to Vitto and Reto for putting me on the right path.

Upvotes: 0

Vitto
Vitto

Reputation: 399

You can join them in this way: 

SELECT fname, surname, CONCAT(surname,fname) AS fullname,  SUM(points) AS total_points, fifteenday_points
FROM tablename JOIN (
    SELECT CONCAT(surname,forename) 'fullname', SUM(points) AS fifteenday_points
    FROM tablename 
    WHERE date >= NOW() - INTERVAL 15 DAY  AND display ='1' 
    GROUP BY fullname) AS fifteen_days
    ON tablename.fullname = fifteen_days.fullname
WHERE YEAR(date) = YEAR(NOW()) AND display ='1' 
GROUP BY fullname
ORDER BY total_points DESC, fullname ASC

I also changed the conditions on datas

Upvotes: 1

Related Questions