CODEWITHSUNDEEP
phpmysqlsqlselect
Kyle R
Kyle R

Reputation: 568

mySQL SUM and Ordering

We have a table which inserts a row each day and updates it with data.

I have the following query to get the total amount of clicks from the table:

SELECT SUM(`total_clicks`) AS clicks, `last_updated` FROM `reporting` WHERE `unique_id` = 'xH7' ORDER BY `last_updated` DESC

When pulling this info from the database, it is pulling the correct total amount of clicks but the last_updated field is from the first row (yesterday) not the new row inserted today.

How can I go about getting the most recent last_updated field?

Upvotes: 1

Views: 30

Answers (2)

Barmar
Barmar

Reputation: 781096

If you want the most recent date, use MAX to select it:

SELECT SUM(total_clicks) as clicks, MAX(last_updated) AS last_updated
FROM reporting
WHERE unique_id = 'xH7'

The problem with your version is that ORDER BY happens after aggregating, and aggregation selects non-aggregate columns from arbitrary rows in the table.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269933

If you have only one row per day, then you don't need sum(). Does the following do what you want?

SELECT `total_clicks` AS clicks, `last_updated`
FROM `reporting`
WHERE `unique_id` = 'xH7'
ORDER BY `last_updated` DESC
LIMIT 1;

Your query is an aggregation query that adds up all the clicks in the table. Because it returns only one row, the order by isn't doing anything.

Upvotes: 1

Related Questions