SQL Query to consolidate rows into new table
Hi again,
I have another query to get my head around!
I am trying to take an existing table in a database that looks like this
Time Type Price Code
Software/Hardware used:
not specified
09:00:01 BID 99 A
09:00:01 ASK 100 A
09:00:01 BID 99.5 B
09:00:01 ASK 100 B
09:00:02 BID 99.3 A
09:00:03 BID 98.5 C
09:00:03 ASK 99 C
and put into another table with this structure, that consolidates BID/ASK values with the same time-stamp and code:
Time BID_PRICE ASK_PRICE Code
09:00:01 99 100 A
09:00:01 99.5 100 B
09:00:02 99.3 NULL A
09:00:03 98.5 99 C
thankyou again for any help!
Software/Hardware used:
not specified
ASKED:
August 11, 2010 11:09 AM
UPDATED:
August 11, 2010 9:37 PM
Answer Wiki:
You could create your new table from a query similar to this:
<pre>SELECT time, SUM(IIF(type="BID",price,0)) AS bid_price, SUM(IIF(type="ASK",price,0)) AS ask_price,code
FROM your_table
GROUP BY time,code
</pre>
This is Access syntax (you tagged the question with 'Access' <b>AND </b>'SQlite', so we don't really know what you are using).
To see all answers submitted to the Answer Wiki: View Answer History.
Hi Carlos,
thankyou very much!!!
literally just replaced my table name and it worked first time!
as a follow up, please may i ask 2 things:
1. what is the purpose of the SUM – why not just the IFF statement on its own, I’m assuming it is so we can do the aggregate group statement but im a little unclear as to why it works, and does not work without it…?
2. In SQlite can I use CASE statements instead of the IFF?
thanks so much… i spent all day trial and error, amounted to nothing
I’m glad it worked.
As for your questions:
1. For each time-code combination, you are consolidating two rows into one. To do that you have to group those two rows using a ‘GROUP BY’ clause.
When you use a ‘GROUP BY’ clause, every column in the select list that is not included in the GROUP BY clause must be an aggregate function. If you omit the aggregate function you would get a syntax error.
That’s why the SUM function is needed.
2. Yes, you could use a CASE expression.