Link to home
Create AccountLog in
Avatar of Wedmore
Wedmore

asked on

Getting the last row AND aggregating a column in SQL

From the table below, I want to be able to show the last line for Stage1, Stage2 and Stage3 but also would like to show the total arrived that day.  I dont think this would be possible to aggregate as well as showing a specific line in one query, any suggestions how I could do this?

What I would like to report is...

Name            Date          Arrived  Stg1  Stg2  Stg3
ProcessA      2/14/2011    24        24        0       1





Name	Date	Time	Arrived	Stage1	Stage2  Stg3
ProcessA	2/14/2011	0:00:00	1	2	0	0
ProcessA	2/14/2011	1:00:00	1	3	0	0
ProcessA	2/14/2011	2:00:00	1	3	1	0
ProcessA	2/14/2011	3:00:00	1	4	1	0
ProcessA	2/14/2011	4:00:00	1	5	1	0
ProcessA	2/14/2011	5:00:00	1	6	1	0
ProcessA	2/14/2011	6:00:00	1	7	1	0
ProcessA	2/14/2011	7:00:00	1	8	1	0
ProcessA	2/14/2011	8:00:00	1	9	1	0
ProcessA	2/14/2011	9:00:00	1	10	1	0
ProcessA	2/14/2011	10:00:00	1	11	1	0
ProcessA	2/14/2011	11:00:00	1	12	1	0
ProcessA	2/14/2011	12:00:00	1	13	1	0
ProcessA	2/14/2011	13:00:00	1	14	0	1
ProcessA	2/14/2011	14:00:00	1	15	0	1
ProcessA	2/14/2011	15:00:00	1	16	0	1
ProcessA	2/14/2011	16:00:00	1	17	0	1
ProcessA	2/14/2011	17:00:00	1	18	0	1
ProcessA	2/14/2011	18:00:00	1	19	0	1
ProcessA	2/14/2011	19:00:00	1	20	0	1
ProcessA	2/14/2011	20:00:00	1	21	0	1
ProcessA	2/14/2011	21:00:00	1	22	0	1
ProcessA	2/14/2011	22:00:00	1	23	0	1
ProcessA	2/14/2011	23:00:00	1	24	0	1

Open in new window

Avatar of incerc
incerc
Flag of Romania image

Hi, you can try an union of the last row with the row containing the SUM(arrived), and then using aggregate functions on that , like this :

1.
SELECT max(name), date, max(arrived), max(stg1), max(stg2), max(stg3)
FROM  
(SELECT name, date, 0, stg1, stg2, stg3 //here is the last row, with 0 for sum(arrived)
FROM mytable
WHERE date = mydate
ORDER BY time desc limit 1  
UNION
SELECT "", date, sum(arrived) as arrived, 0, 0, 0 //here is the sum
FROM mytable
WHERE date = mydate
GROUP BY date)
GROUP by date

you will have :
Name            Date          Arrived  Stg1  Stg2  Stg3
ProcessA      2/14/2011    0        24        0       1
""                 2/14/2011    24        0         0      0

Into this temporary table, you have to apply the MAX aggregate functions.

2. Another solution might be below, if you don't need the stg1-3 from the last row, and if the name is the same for a given date (as in your example).

SELECT name, date, sum(arrived) as arrived
FROM mytable
GROUP BY name, date

Hope this helps.
ASKER CERTIFIED SOLUTION
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
What dbms are you using?
Avatar of Wedmore
Wedmore

ASKER

Perfect.

Is there any "major" performance or efficiency degradation when using derived tables?
Versus the alternatives available in most systems for what you need to do here, I would have to say no.  If you are using a system like Access that has a LAST aggregate function, then you may see slight degrade as you are reading whatever table it is twice using derived method.  With proper indexing, this method should perform well for you IMHO.