Solved

Getting the last row AND aggregating a column in SQL

Posted on 2011-02-14
5
331 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:Wedmore
5 Comments
 
LVL 4

Expert Comment

by:incerc
ID: 34891294
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.
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 250 total points
ID: 34897294
See if this works for you, using a derived table aggregating some of the data while getting the max time to associate back to last row.
SELECT a.Name, a.Date, b.SUM_Arrived, a.Stg1, a.Stg2, a.Stg3
FROM YourTable a
JOIN (
   SELECT Name, Date, MAX(Time) AS MAX_Time
        , SUM(Arrived) AS SUM_Arrived
   FROM YourTable
   GROUP BY Name, Date
) b ON b.Name = a.Name AND b.Date = a.Date AND b.MAX_Time = a.Time

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 34899329
What dbms are you using?
0
 

Author Closing Comment

by:Wedmore
ID: 34901185
Perfect.

Is there any "major" performance or efficiency degradation when using derived tables?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 34901267
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.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MYSQL responding very slow 3 48
SQL Server syntax 11 40
removing unwanted rows from an sql server ranked table 13 34
StoredProcedure to JSON query faulty syntax 2 22
As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question