Hi,
New to the forum but have taken lots of advice over the years. I am looking for help with a group by problem I am having. See example list of orders below.
---------------------------------------------------------------------------------------------------
control name street city country order_date order_amt
---------------------------------------------------------------------------------------------------
123456 John Doe 123 New Street Capital City Whereeverland 05/03/2011 100
123456 John Doe 123 New Street Capital City Whereeverland 06/04/2011 200
123456 John Doe 123 New Street Capital City Whereeverland 08/05/2011 100
123456 John Doe 123 New Street Capital City Whereeverland 09/06/2011 200
123456 John Doe 123 New Street Capital City Whereeverland 11/07/2011 100
123456 John Doe 789 Old Street Second City Whereeverland 12/08/2011 200
123456 John Doe 789 Old Street Second City Whereeverland 13/09/2011 100
123456 John Doe 789 Old Street Second City Whereeverland 15/10/2011 200
123456 John Doe 789 Old Street Second City Whereeverland 16/11/2011 100
---------------------------------------------------------------------------------------------------
If I do a regular GROUP BY I can get them to come out like so no problem.
---------------------------------------------------------------------------------------------------
control name street city country count(orders) sum(order_amt)
---------------------------------------------------------------------------------------------------
123456 John Doe 123 New Street Capital City Whereeverland 5 700
123456 John Doe 789 Old Street Second City Whereeverland 4 600
---------------------------------------------------------------------------------------------------
My question is whether it is possible to group them just based on one field (in this case the control number and then use the most frequent for each other field in this case the address with 5 orders against it rather than 4. I assume a subquery should help me here but I can't figure it.
---------------------------------------------------------------------------------------------------
control name street city country count(orders) sum(order_amt)
---------------------------------------------------------------------------------------------------
123456 John Doe 123 New Street Capital City Whereeverland 9 1300
---------------------------------------------------------------------------------------------------
If anyone can give me some pointers on the sql for this I would be very grateful.
Thanks
pentesh
New to the forum but have taken lots of advice over the years. I am looking for help with a group by problem I am having. See example list of orders below.
---------------------------------------------------------------------------------------------------
control name street city country order_date order_amt
---------------------------------------------------------------------------------------------------
123456 John Doe 123 New Street Capital City Whereeverland 05/03/2011 100
123456 John Doe 123 New Street Capital City Whereeverland 06/04/2011 200
123456 John Doe 123 New Street Capital City Whereeverland 08/05/2011 100
123456 John Doe 123 New Street Capital City Whereeverland 09/06/2011 200
123456 John Doe 123 New Street Capital City Whereeverland 11/07/2011 100
123456 John Doe 789 Old Street Second City Whereeverland 12/08/2011 200
123456 John Doe 789 Old Street Second City Whereeverland 13/09/2011 100
123456 John Doe 789 Old Street Second City Whereeverland 15/10/2011 200
123456 John Doe 789 Old Street Second City Whereeverland 16/11/2011 100
---------------------------------------------------------------------------------------------------
If I do a regular GROUP BY I can get them to come out like so no problem.
---------------------------------------------------------------------------------------------------
control name street city country count(orders) sum(order_amt)
---------------------------------------------------------------------------------------------------
123456 John Doe 123 New Street Capital City Whereeverland 5 700
123456 John Doe 789 Old Street Second City Whereeverland 4 600
---------------------------------------------------------------------------------------------------
My question is whether it is possible to group them just based on one field (in this case the control number and then use the most frequent for each other field in this case the address with 5 orders against it rather than 4. I assume a subquery should help me here but I can't figure it.
---------------------------------------------------------------------------------------------------
control name street city country count(orders) sum(order_amt)
---------------------------------------------------------------------------------------------------
123456 John Doe 123 New Street Capital City Whereeverland 9 1300
---------------------------------------------------------------------------------------------------
If anyone can give me some pointers on the sql for this I would be very grateful.
Thanks
pentesh