[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Getting the last row AND aggregating a column in SQL

Posted on 2011-02-14
5
Medium Priority
?
338 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 60

Accepted Solution

by:
Kevin Cross earned 1000 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 60

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

656 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