CODEWITHSUNDEEP
phpmysql
user3787390
user3787390

Reputation: 41

MySQL SUM data in column "A" when data in column "B" matches

My table is as follows:

Year    Week   Points
2007     1       20
2007     1       15
2007     2       15
2007     2       15
2008     1       8
2008     1       10

I want my outcome to be:

Most Points: 35 (2007 Week 1)
2nd Most Points: 30 (2007 Week 2)
3rd Most Points: 18 (2008 Week 1)

I'm using:

$query = "SELECT SUM(POINTS) ";
$query .= " FROM SCHEDULE";
$query .= " AND WEEK = '1' ";
$query .= " AND YEAR = '2008'";

Is there a way to get the points SUM w/o having to run separate queries for each week and year?

Upvotes: 0

Views: 35

Answers (3)

VMai
VMai

Reputation: 10346

Your query should be

SELECT
    SUM(Points) total,
    CONCAT(`Year`, ' Week ', `Week`)
FROM
    SCHEDULE
GROUP BY
    `Year`,
    `Week`,
ORDER BY
    total DESC

Upvotes: 0

slugonamission
slugonamission

Reputation: 9642

You can use GROUP BY for this, for example:

SELECT YEAR, MONTH, SUM(POINTS) FROM SCHEDULE GROUP BY WEEK,YEAR

That will then form groups of items with the same WEEK and YEAR, then sum across those.

Upvotes: 1

nl-x
nl-x

Reputation: 11832

This sounds like a simple query. Have you tried this? :

select `Year`, `Week`, sum(`Points`) as Total_Points
from myTable
group by `Year`, `Week`
order by sum(`Points`) desc
limit 3

Upvotes: 0

Related Questions