[SOLVED] Sum and Ordering at the same time

[plautzer]

Hi,

 

I cant get a this query to work... maybe some of you can help

 

I am trying to get the last row of a certain period and sum a column of this period at the same time.

 

SELECT *, sum(number) as dis
FROM table
WHERE id = 1000
AND cdate > '2008-12-12'
Group by id
ORDER by id DESC limit 1

 

When Im processing this query I wont get the latest row. Do u know why that is?

 

Thx,

Plautzer

[kickstart]

Hi

 

If you use function like SUM with a GROUP BY clause you need to specify in the GROUP BY all the columns you want returned which are not in the SUM.

 

Most flabours of SQL will just give an error if you try to just bring back non GROUP BY / aggregate columns, while MySQL will just bring back a random rows value for those fields.

 

Think you just want a single row back, so try this:-

 

SELECT MAX(id), SUM(number) as dis
FROM table
WHERE id = 1000
AND cdate > '2008-12-12'

 

All the best

 

Keith

[plautzer]

If you use function like SUM with a GROUP BY clause you need to specify in the GROUP BY all the columns you want returned which are not in the SUM.

 

Meaning, that it is not possible to get the values of the last row?

 

SELECT columnA, columnB, columnC, SUM(number) as dis

[kickstart]

Hi

 

Not simple.

 

To get it what you need to do is something like get the max value and some an id, then do a join against the same table on those fields to get the other fields.

 

Normally not difficult but would need a bit more detail to say exactly what you need here.

 

All the best

 

Keith

[plautzer]

HI,

 

thats what i exactly need:

 

first... i want to get 2 latest values of a certain foreign id ( multiple same ids in table) within a certain period of time:

 

SELECT last_update, value
FROM `table`
WHERE id = 1000
AND cdate > '2008-12-12'
Order by cdate DESC

 

and second.. i need the third value 'sum of number' the id had within the same time period of the exact same table:

 

SELECT sum(number)
FROM `table`
WHERE id = 1000
AND cdate > '2008-12-12'
Group by id

 

And i want to combine that in one query.

 

[kickstart]

Hi

 

Something like this would do it:-

 

SELECT a.id, a.value, b.maxdate, b.sumnumber
FROM `table` a
JOIN (SELECT id, MAX(cdate) AS maxdate, SUM(number) AS sumnumber
FROM `table`
WHERE id = 1000
AND cdate > '2008-12-12'
Group by id) b
ON a.id = b.id
AND a.cdate = b.maxdate

 

If cdate for an id isn't unique then it will have problems!

 

All the best

 

Keith

[plautzer]

Hi,

I tried it on my table and it works like charm.

But will it also work on Temporary Tables? I heard that a self join isnt possible.

 

 

[kickstart]

Hi

 

Never tried but seen the same thing about temp tables.

 

All the best

 

Keith

[plautzer]

I just had the chane to try it out... works good on temp tables as well.

 

Thx!