Solved

Getting the last row AND aggregating a column in SQL

Posted on 2011-02-14
5
330 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Union 2 queries to a cte (temp table perhaps) 9 41
SQL Sub-Query Help 22 67
SQL Server 2012 r2 - calculations/operation on many Temp Tables 6 26
Trouble with <> 2 20
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

791 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