# SQL help. **URL:** \ **Category:** Factual Questions\ **Created:** [March 7, 2008, 6:32pm UTC](https://boards.straightdope.com/t/sql-help/440477 "2008-03-07T18:32:31Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Lobsang](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lobsang/32/4067_2.png) [@Lobsang](https://boards.straightdope.com/u/Lobsang)\ **Post date:** [March 7, 2008, 6:32pm UTC](https://boards.straightdope.com/t/sql-help/440477/1 "2008-03-07T18:32:31Z")
I asked this before and nobody was able to help. So I figured I’d ask a ‘human’ question and someone can attempt to convert it into SQL. but before I do that… What is the technical name (if there is one) for a query that combines a sum from grouped data with a first and/or last entry of a specific field for that data? For the sake of argument I have a table with account number, balance forward, and amount. I want the SUM of the amount field, and the FIRST and LAST value of balance forward field for each account. so my ‘english’ question is this - “give me the first value for balance forward, the last value for balance forward, a sum of amount, where the date is yesterday, for each account” The annoying thing is the above should be a straight-forward (and commonly required) thing to do… but the SQL is proving hard for me to work out. Unless I am missing some obvious method. Halp! ---
**Author:** ![Turek](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/turek/32/18782_2.png) [@Turek](https://boards.straightdope.com/u/Turek)\ **Post date:** [March 7, 2008, 6:40pm UTC](https://boards.straightdope.com/t/sql-help/440477/2 "2008-03-07T18:40:17Z")
… ---
**Author:** ![Lobsang](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lobsang/32/4067_2.png) [@Lobsang](https://boards.straightdope.com/u/Lobsang)\ **Post date:** [March 7, 2008, 6:43pm UTC](https://boards.straightdope.com/t/sql-help/440477/3 "2008-03-07T18:43:53Z")
[QUOTE=Turek] First and Last value of BalanceForward based on what criteria? Is there a date column you haven’t mentioned? [/QUOTE] Sorry, the data is in time order… So the top would be the balance at the beginning of the day, and the bottom would be the balance at the end of the day. for one day (yesterday) for all accounts (so it’s grouped… so I end up with three values for each account - a start bal, an end bal, and a sum of the amount) ---
**Author:** ![wolfman](https://avatars.discourse-cdn.com/v4/letter/w/a8b319/32.png) [@wolfman](https://boards.straightdope.com/u/wolfman)\ **Post date:** [March 7, 2008, 6:46pm UTC](https://boards.straightdope.com/t/sql-help/440477/4 "2008-03-07T18:46:13Z")
Could you show the desciption for the table you are working with? ---
**Author:** ![Lobsang](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lobsang/32/4067_2.png) [@Lobsang](https://boards.straightdope.com/u/Lobsang)\ **Post date:** [March 7, 2008, 6:53pm UTC](https://boards.straightdope.com/t/sql-help/440477/5 "2008-03-07T18:53:31Z")
[QUOTE=wolfman] Could you show the desciption for the table you are working with? [/QUOTE] You mean all the fields, their data types, sizes etc? There are a lot of fields. Only three of them are relevant to my GQ - Account number, balance forward (which is different for each record) and amount. If it helps - it’s a table of bet transactions, each record corresponds to one bet. Each record updates the ‘balance\_forward’ field. ---
**Author:** ![Turek](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/turek/32/18782_2.png) [@Turek](https://boards.straightdope.com/u/Turek)\ **Post date:** [March 7, 2008, 6:56pm UTC](https://boards.straightdope.com/t/sql-help/440477/6 "2008-03-07T18:56:24Z")
SELECT AccountNumber, (SELECT TOP 1 BalanceForward FROM table1 WHERE AccountNumber = T.AccountNumber AND DateEntered \>= GetDate() ORDER BY DateEntered ASC) AS First, (SELECT TOP 1 BalanceForward FROM table1 WHERE AccountNumber = T.AccountNumber AND DateEntered \>= GetDate() ORDER BY DateEntered DESC) AS Last, SUM(Amount) As SumAmount FROM table1 T WHERE DateEntered \>= GetDate() GROUP BY AccountNumber You’ll have to jiggle with the date stuff to get yesterday’s data and it depends on your your particular database server. ---
**Author:** ![wolfman](https://avatars.discourse-cdn.com/v4/letter/w/a8b319/32.png) [@wolfman](https://boards.straightdope.com/u/wolfman)\ **Post date:** [March 7, 2008, 6:58pm UTC](https://boards.straightdope.com/t/sql-help/440477/7 "2008-03-07T18:58:52Z")
Well basically I wondered it you have a timestamp for each transaction ---
**Author:** ![Punoqllads](https://avatars.discourse-cdn.com/v4/letter/p/d2c977/32.png) [@Punoqllads](https://boards.straightdope.com/u/Punoqllads)\ **Post date:** [March 7, 2008, 6:59pm UTC](https://boards.straightdope.com/t/sql-help/440477/8 "2008-03-07T18:59:37Z")
Which flavor of SQL are you using? Oracle? MySQL? Microsoft? Different flavors will have different answers. ---
**Author:** ![Ponderoid](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/ponderoid/32/19221_2.png) [@Ponderoid](https://boards.straightdope.com/u/Ponderoid)\ **Post date:** [March 7, 2008, 7:00pm UTC](https://boards.straightdope.com/t/sql-help/440477/9 "2008-03-07T19:00:31Z")
[QUOTE=Lobsang] What is the technical name (if there is one) for a query that combines a sum from grouped data with a first and/or last entry of a specific field for that data? [/quote] That’s a [correllated subquery](http://www.databasejournal.com/features/mssql/article.php/3485291). The specific example is for MS-SQL/T-SQL, but it’s pretty universal and supported by the ANSI-92 standard. \*\*\* Ponder ---
**Author:** ![ratatoskK](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/ratatoskk/32/2987_2.png) [@ratatoskK](https://boards.straightdope.com/u/ratatoskK)\ **Post date:** [March 7, 2008, 7:04pm UTC](https://boards.straightdope.com/t/sql-help/440477/10 "2008-03-07T19:04:37Z")
That’s interesting, I mostly use Oracle and never heard of it, and just found [this page](http://dotnet.org.za/thea/archive/2005/02/22/14715.aspx) that talks a little about different platforms. ---
**Author:** ![Ponderoid](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/ponderoid/32/19221_2.png) [@Ponderoid](https://boards.straightdope.com/u/Ponderoid)\ **Post date:** [March 7, 2008, 7:09pm UTC](https://boards.straightdope.com/t/sql-help/440477/11 "2008-03-07T19:09:38Z")
A properly designed correlated subquery shouldn’t need to resort to DB-specific TOP/LIMIT/ROWNUM tricks, as long as the data has decent recognizable ordering, such as a datetime or sequence number on each row. \*\*\* Ponder ---
**Author:** ![Lobsang](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lobsang/32/4067_2.png) [@Lobsang](https://boards.straightdope.com/u/Lobsang)\ **Post date:** [March 7, 2008, 7:25pm UTC](https://boards.straightdope.com/t/sql-help/440477/12 "2008-03-07T19:25:06Z")
[QUOTE=wolfman] Well basically I wondered it you have a timestamp for each transaction [/QUOTE] Yes. Each record has an exact timestamp… so the first record will be the earliest, and vice versa. ---
**Author:** ![Lobsang](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lobsang/32/4067_2.png) [@Lobsang](https://boards.straightdope.com/u/Lobsang)\ **Post date:** [March 7, 2008, 7:31pm UTC](https://boards.straightdope.com/t/sql-help/440477/13 "2008-03-07T19:31:55Z")
… ---
**Author:** ![Manduck](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/manduck/32/256_2.png) [@Manduck](https://boards.straightdope.com/u/Manduck)\ **Post date:** [March 7, 2008, 7:42pm UTC](https://boards.straightdope.com/t/sql-help/440477/14 "2008-03-07T19:42:30Z")
I would try: ```auto SELECT accountnumber, SUM(amount), (SELECT balance FROM thetable WHERE timestamp = (SELECT MIN(timestamp) FROM thetable WHERE accountnumber = T.account_number)) AS balance_start, (SELECT balance FROM thetable WHERE timestamp = (SELECT MAX(timestamp) FROM thetable WHERE accountnumber = T.account_number)) AS balance_end FROM thetable T GROUP BY accountnumber ``` ---
**Author:** ![Ponderoid](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/ponderoid/32/19221_2.png) [@Ponderoid](https://boards.straightdope.com/u/Ponderoid)\ **Post date:** [March 7, 2008, 7:54pm UTC](https://boards.straightdope.com/t/sql-help/440477/15 "2008-03-07T19:54:03Z")
**Manduck** beat me to it. I apologize for that earlier link; I didn’t read it carefully. The author of that guide used the TOP device in their examples, which is fine for T-SQL, but that might not be the DB-flavor you’re using. **Manduck’s** example should work on any SQL-92-compliant database. \*\*\* Ponder ---
**Author:** ![Tool\_of\_the\_Conspiracy](https://avatars.discourse-cdn.com/v4/letter/t/bc79bd/32.png) [@Tool\_of\_the\_Conspiracy](https://boards.straightdope.com/u/Tool_of_the_Conspiracy)\ **Post date:** [March 7, 2008, 8:07pm UTC](https://boards.straightdope.com/t/sql-help/440477/16 "2008-03-07T20:07:00Z")
Am I missing something? If the rows are guaranteed to be in the correct order, it seems like this should do the trick: ```auto SELECT AccountNumber, First(BalanceForward) AS Start, Last(BalanceForward) AS End, Sum(Amount) AS Total FROM Table WHERE TimeStamp = Date()-1 GROUP BY AccountNumber ``` Those functions aren’t Microsoft-specific, are they? (I know that Date() is, but there’s sure to be something like it in any language.) If the rows aren’t always in the right order, it’s slightly more complicated, but I’d try to put a single subquery in the FROM clause, not two in the SELECT clause. I may be way off, because I’m mostly self-taught. ---
**Author:** ![wolfman](https://avatars.discourse-cdn.com/v4/letter/w/a8b319/32.png) [@wolfman](https://boards.straightdope.com/u/wolfman)\ **Post date:** [March 7, 2008, 8:12pm UTC](https://boards.straightdope.com/t/sql-help/440477/17 "2008-03-07T20:12:49Z")
```auto SELECT accountnumber, SUM(amount), (SELECT balance FROM thetable WHERE timestamp = (SELECT MIN(timestamp) FROM thetable WHERE accountnumber = T.account_number)) AS balance_start, (SELECT balance FROM thetable WHERE timestamp = (SELECT MAX(timestamp) FROM thetable WHERE accountnumber = T.account_number)) AS balance_end FROM thetable T GROUP BY accountnumber ``` I don’t believe that takes yesterday into account. I’t will find the life of the account. ```auto SELECT accountnumber, SUM(amount), (SELECT balance FROM thetable WHERE timestamp = (SELECT MIN(timestamp) FROM thetable WHERE accountnumber = T.account_number AND timestamp like (to_date("DD-MON-YYYY"), (SYSDATE -1)) )) AS balance_start, (SELECT balance FROM thetable WHERE timestamp = (SELECT MAX(timestamp) FROM thetable WHERE accountnumber = T.account_number AND timestamp like (to_date("DD-MON-YYYY"), (SYSDATE -1)) )) AS balance_end FROM thetable T GROUP BY accountnumber ``` ---
**Author:** ![wolfman](https://avatars.discourse-cdn.com/v4/letter/w/a8b319/32.png) [@wolfman](https://boards.straightdope.com/u/wolfman)\ **Post date:** [March 7, 2008, 8:24pm UTC](https://boards.straightdope.com/t/sql-help/440477/18 "2008-03-07T20:24:25Z")
Gah stupid timeout ```auto SELECT accountnumber, SUM(amount), (SELECT balance FROM thetable WHERE timestamp = (SELECT MIN(timestamp) FROM thetable WHERE accountnumber = T.account_number AND timestamp like (to_date("DD-MON-YYYY"), (SYSDATE -1)) )) AS balance_start, (SELECT balance FROM thetable WHERE timestamp = (SELECT MAX(timestamp) FROM thetable WHERE accountnumber = T.account_number AND timestamp like (to_date("DD-MON-YYYY"), (SYSDATE -1)) )) AS balance_end FROM thetable T WHERE timestamp like (to_date("DD-MON-YYYY"), (SYSDATE -1)) GROUP BY accountnumber ``` ---
**Author:** ![Ponderoid](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/ponderoid/32/19221_2.png) [@Ponderoid](https://boards.straightdope.com/u/Ponderoid)\ **Post date:** [March 7, 2008, 8:32pm UTC](https://boards.straightdope.com/t/sql-help/440477/19 "2008-03-07T20:32:03Z")
First() and Last() sound like functions specific to some particular DB-flavor, and shouldn’t be relied upon. The OP never specified what DB they were using, unless I missed it? The Date()-1 construct is also DB-specific. I hate translating date functions between DB flavors; they aren’t covered in the SQL-92 standard, so everybody does them differently. One should never assume the rows are in a specific order in the database, this is why we have key and index fields. Because the OP’s requirement is to join on 2 different rows, I don’t think there’s any way around doing 2 different subselects to get the data they want. **Wolfman** ’s altered example also relies on DB-specific date functions. **Lobsang** will have to look up the correct syntax for their DB, and also do whatever tricks are necessary to split out the day component of the datetime to cover one specific date. \*\*\* Ponder ---
**Author:** ![Tool\_of\_the\_Conspiracy](https://avatars.discourse-cdn.com/v4/letter/t/bc79bd/32.png) [@Tool\_of\_the\_Conspiracy](https://boards.straightdope.com/u/Tool_of_the_Conspiracy)\ **Post date:** [March 7, 2008, 8:51pm UTC](https://boards.straightdope.com/t/sql-help/440477/20 "2008-03-07T20:51:38Z")
[QUOTE=Ponderoid] First() and Last() sound like functions specific to some particular DB-flavor, and shouldn’t be relied upon. The OP never specified what DB they were using, unless I missed it? The Date()-1 construct is also DB-specific. I hate translating date functions between DB flavors; they aren’t covered in the SQL-92 standard, so everybody does them differently. One should never assume the rows are in a specific order in the database, this is why we have key and index fields. Because the OP’s requirement is to join on 2 different rows, I don’t think there’s any way around doing 2 different subselects to get the data they want. **Wolfman** ’s altered example also relies on DB-specific date functions. **Lobsang** will have to look up the correct syntax for their DB, and also do whatever tricks are necessary to split out the day component of the datetime to cover one specific date. \*\*\* Ponder [/QUOTE] Yeah, I took it as a given that anything date-related would need to be translated, and have conversion functions added. Here’s my single-subquery version, without First, Last, or the assumption of record-order. It does assume that each TimeStamp is unique. ```auto SELECT minmax.AccountNumber, small.BalanceForward AS Start, big.BalanceForward AS End, minmax.Total FROM Table AS small INNER JOIN ( [SELECT AccountNumber, Min(TimeStamp) AS FirstTime, Max(TimeStamp) AS LastTime, Sum(Amount) AS Total FROM Table WHERE TimeStamp = Date()-1 GROUP BY AccountNumber]. AS minmax INNER JOIN Table AS big ON minmax.AccountNumber = big.AccountNumber AND minmax.LastTime = big.TimeStamp ) ON small.AccountNumber = minmax.AccountNumber AND small.TimeStamp = minmax.FirstTime ``` [Next page](https://boards.straightdope.com/t/sql-help/440477.md?page=2)