question

praveenm avatar image
praveenm asked

grouping based on critiria

I have a customer order log table like bellow Orderid lgdate amount type 1001 01/01/2015 0 Advance 1002 02/01/2015 200 Advance 1001 03/01/2015 300 Balance 1003 03/01/2015 200 Advance 1003 05/01/2015 0 Balance 1002 06/01/2015 250 Balance One customer order has one advance and one balance. Each customer order will close when it's balance received even it's balance is 'zero'. total amount of customer order is advance+balance.If I select a date i need to know how many orders are closed in that day. eg.In 06/01/2015 order 1002 closed and total amount is 450. like 1002 06/01/2015 450. how to write a workable sql code for the above result.
sql-server-2008sql
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
why has someone downvoted this question without leaving any comment?
1 Like 1 ·
Gazz avatar image Gazz commented ·
Sorry, that was me. Fat fingers
1 Like 1 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Not sure. I went ahead and voted it up to offset that. Seemed odd.
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
0 Likes 0 ·
David Wimbush avatar image
David Wimbush answered
Something like this should work: declare @date date = '06/01/2015' --Needs a less ambiguous date format select a.Orderid , a.amount as Advance , b.amount as Balance , a.amount + b.amount as Total from ( select Orderid , amount from tablename where type = 'Advance' and lgdate = @date ) a inner join ( select Orderid , amount from tablename where type = 'Balance' and lgdate = @date ) b on b.Orderid = a.Orderid order by a.OrderId;
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Gazz avatar image
Gazz answered
This will group the OrderId's together and give you the total SELECT OrderID, SUM(amount)[Total] FROM TABLE GROUP BY OrderID if you join his back onto the table you can then get your result: SELECT * FROM TABLE x JOIN ( SELECT OrderID, SUM(amount)[Total] FROM TABLE GROUP BY OrderID ) y ON x.OrderID= y.OrderID WHERE lgdate= '2015-01-06' AND type = 'Balance'
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.