Solved

Getting the last row AND aggregating a column in SQL

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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. …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

911 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now