SQL Query to consolidate rows into new table

20 pts.
Microsoft Access
SQL Query
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
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

This is Access syntax (you tagged the question with ‘Access’ <b>AND </b>’SQlite’, so we don’t really know what you are using).

Discuss This Question: 2  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Baatch
    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 :)
    20 pointsBadges:
  • carlosdl
    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.
    85,430 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: