June 18, 2006 at 4:19 am
Hi
I have the folowing database in a table for example. im not sure how best to write the sql command
What i want it to do is count the number of rows in the column 'bid' that correspond to the same session id (for example the below shows 4 rows all with 423400408 as the session id). Within also i than like to calculate each product by * quantity than adding them all together (making sure there all within the same session - 4 products (300*1) + (400 *5) + (600*4) + (150*2).
Anyone please assist and i hope that explains it
| bid | item_id | quantity | session_id | price | time_stamp |
| 1 | 23 | 1 | 423400408 | 300.00 | 12/03/2005 8:45:54 |
| 2 | 25 | 5 | 423400408 | 400.00 | 12/03/2005 8:45:54 |
| 3 | 21 | 4 | 423400408 | 600.00 | 12/03/2005 8:45:54 |
| 4 | 13 | 2 | 423400408 | 150.00 | 12/03/2005 8:45:54 |
| 5 | 6 | 5 | 423400501 | 200.00 | 12/03/2005 8:45:54 |
June 18, 2006 at 11:03 am
SELECT Bids.Session_ID, Count(Bids.Bid) AS CountOfBid, Sum([Price]*[Quantity]) AS PriceTimeQuantity
FROM Bids
GROUP BY Bids.Session_ID;
Would that get what you're after? Counts the bids by session id at the same time it calculates and sums price times quantity.
June 18, 2006 at 2:48 pm
Thank you so much
But why this statement
Count(Bids.Bid) why count Bids.bid. Im confuded with 'bids' added
June 18, 2006 at 10:27 pm
You wanted a count of how many bids were included in the same session_id. that's teh count. YOu can actually count any column that is guaranteed non-NULL.
June 18, 2006 at 11:54 pm
i understand now. thank you once again
June 19, 2006 at 12:29 pm
You're welcome.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply