?
Solved

How can I return first and last values of a field in a group?

Posted on 2010-01-03
6
Medium Priority
?
220 Views
Last Modified: 2013-12-13
I have raw data (1 million rows) that is structured as follows:

TICKER          DATE          COMPANY          PRICE          CHANGE
ABC              01012009    ABC Inc.             9.99              0.00
ABC              01022009    ABC Inc.             8.88             -1.11
ABC              01032009    ABC Inc.             7.77             -1.11
CBA              01012009    CBA Inc.             5.55              0.00
CBA              01022009    CBA Inc.             8.88              3.33
CBA              01032009    CBA Inc.             3.33             -6.66
XYZ              01012009    XYZ Inc.             8.88              0.00
XYZ              01022009    XYZ Inc.             5.55             -5.55
XYZ              01032009    XYZ Inc.             2.22             -3.33

On a web page, a user selects a date range so he can see the best performing tickers (best at the top, worst at bottom), grouped by ticker. In this case he selects from: 01012009 to 01032009:

Web Display::
TICKER          CHANGE
ABC               -2.22
CBA               -3.33
XYZ               -8.88

I cant work out how to get a 3rd column showing the percent change of PRICE for the selected date range. (eg from 01012009 to 01032009)

In PHP I have a query that is written as follows.

$query = "SELECT TICKER, DATE, COMPANY, PRICE, SUM(MONETARY_CHANGE) AS 'CHANGE' FROM alldata WHERE DATE >= '$fromdate' AND DATE <= '$todate' GROUP BY TICKER ORDER BY SUM(MONETARY_CHANGE) DESC";

It works fine, but I cannot work out how to get the fist and last PRICE values from each group so I can make a calculation. Not sure if it is best to capture this in SQL or when my php array starts to output later in the page.
0
Comment
Question by:chris_bc
6 Comments
 
LVL 30

Expert Comment

by:Reza Rad
ID: 26166310
could you bring a sample expected result?
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 26166931
You might be able to select MIN() and MAX() on these fields, then use the selected values in the computations.  Or you could do a CREATE TEMPORARY TABLE ENGINE=MEMORY to isolate the date range.  If you do that you are working on a much smaller data set, and a multi-query operation will not be time-consuming at all.

One other thought, instead of this:
WHERE DATE >= '$fromdate' AND DATE <= '$todate'

... try something like this:
WHERE DATE BETWEEN '$fromdate' AND '$todate'

Doing that will cut the number of discreet steps for the date comparisons in half.

And one final thought - DATE is a reserved word in MySQL.  You probably want to change that column name before it causes you trouble.

Best regards, ~Ray
0
 

Author Comment

by:chris_bc
ID: 26167107
Not sure if MIN() and MAX() would work as I don't need the lowest and highest values in each group - just the first and last. Need some way to calculate % difference between the first Price and last Price for a given stock stock in a selected date range.

I wonder if there's some way to incorporate into my existing query something like select price where date = min(date) and the max equivalent.(or some kind of nested select statement that would make my head spin).

Thanks for the tip on DATE. Will change fieldname.

Oh, and expected result (if user selects range Jan1 to Jan3) would be:

TICKER          CHANGE       CHANGE(%)
ABC               -2.22            -22.22%
CBA               -3.33            -40.00%  
XYZ               -8.88            -75.00%

I can generate TICKER and CHANGE, but cant do the 3rd.
0
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!

 
LVL 111

Expert Comment

by:Ray Paseur
ID: 26167172
I was thinking more about using MIN and MAX on the datetime fields to isolate the values on those dates.  But maybe the easiest way to do this would be to combine the PHP and MySQL, letting the query get you two values - one for the first date and one for the last date.  Then you can use PHP to iterate over the results set (not much iterating - only two fields involved) and compute the delta.
0
 

Accepted Solution

by:
chris_bc earned 0 total points
ID: 26172603
My apologies - the raw data set was slightly incorrect. The raw set is below.
I have found a way to get first and last values in the group:

a simple SELECT PRICE AS 'FIRST_PRICE''... will get the first price if the data is grouped by ticker.
To get the last price, it is PRICE + SUM(CHANGE) AS 'LAST_PRICE' from alldata WHERE DATE BETWEEN '$fromdate' AND '$todate' GROUP BY TICKER. From there, I can just make a percentage calculation in the same statement.

Seems to work ok.

TICKER          DATE          COMPANY          PRICE          CHANGE
ABC              01012009    ABC Inc.             9.99              0.00
ABC              01022009    ABC Inc.             8.88             -1.11
ABC              01032009    ABC Inc.             7.77             -1.11
CBA              01012009    CBA Inc.             5.55              0.00
CBA              01022009    CBA Inc.             8.88              3.33
CBA              01032009    CBA Inc.             3.33             -5.55
XYZ              01012009    XYZ Inc.             8.88              0.00
XYZ              01022009    XYZ Inc.             5.55             -3.33
XYZ              01032009    XYZ Inc.             2.22             -3.33
0
 
LVL 9

Expert Comment

by:jamesgu
ID: 26176238
to make life easier, you can use change / 'first price' ( or 1 - change / 'first price' depends on what you want ) to get CHANGE(%), you don't need last prices
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

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.
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month16 days, 18 hours left to enroll

862 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