Ailsom, Forgive me as it is Friday but I can't understand exactly what you want to end up with. If you want to simply group by shop with a sum of Ticket_Total this is no problem at all without having a sub query so why do it?
The sub Query in your example (You need to add an "alias" after the "(select distinct...)" by the way) is irrelevant. Or am I being really really stupid here. Also as Andy says the null will simply evaluate to zero in a query. Maybe I should just go to the pub. Dave Crozier An example of the output required would help. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ailsom F. Heringer (Osklen) Sent: 09 March 2007 13:55 To: [email protected] Subject: Re: SUM + GROUP BY ??? Dave, The "item_total" column is not consistent. That´s why I cannot use it. I need to group by "shop" (not "shop" and "ticket"), but i have to get only one " ticket_total" for each "ticket", since "ticket_total" is replicated for all items I can see a solution using sub-queries, but i don´t know if there is a better way: SELECT Shop,SUM(Ticket_Total) FROM (SELECT DISTINCT Shop,Ticket,Ticket_Total FROM T1) GROUP BY Shop (The performance is too bad ..) -- Aílsom F. Heringer [EMAIL PROTECTED] Skype: ailsom.osklen Analista de Sistemas ---------------------------------- Osklen Departamento de Informática Rio de Janeiro - RJ BRASIL http://www.osklen.com.br 55 21 22198971 Dave Crozier escreveu: > Ailsom, > The Null value is not a problem if you use NVL() > > Is this what you mean as I can't see a problem here? > > Create Cursor T1 (Shop C(2), Ticket C(3), Item C(2), Ticket_Total N(9,2), > Item_Total N(9,2) null) > > Insert Into T1 Values ("01","001", "01", 100.50, 25.5) > Insert Into T1 Values ("01","001", "02", 100.50, 75.5) > * > Insert Into T1 Values ("02","005", "01", 32.00, 15.5) > Insert Into T1 Values ("02","005", "02", 32.00, 10.0) > Insert Into T1 Values ("02","005", "03", 32.00, 7.0) > Insert Into T1 Values ("02","006", "02", 32.00, 10.0) > Insert Into T1 Values ("02","006", "03", 32.00, 7.0) > * > Insert Into T1 Values ("03","005", "03", 32.00, null) > > > Select Distinct ; > Shop, ; > Ticket, ; > Sum(Ticket_Total), ; > Sum(Nvl(Item_Total,0)) ; > >From T1 ; > Into Cursor curResult ; > Group By Shop, Ticket > * > Browse > > Dave Crozier > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf > Of Ailsom F. Heringer (Osklen) > Sent: 09 March 2007 12:47 > To: [email protected] > Subject: SQL: SUM + GROUP BY ??? > > Foxers > > I have a table like below: > > ShopNO TicketNO ItemNO TicketTotal ItemTotal > 01 001 01 > 100.50 25.50 > 01 001 02 > 100.50 75.00 > 02 005 01 32.00 > 15.00 > 02 005 02 32.00 > 10.00 > 02 005 03 32.00 > 7.00 > > I have write a SELECT command to sum "TicketTotal" grouping by "ShopNo" > with distinct values of "TicketTotal" based on "ShopNo" and "TicketNO". > > I cannot use "ItemTotal", because in some records this column is null. > > Can someone help me with this syntax ? > > -- Aílsom F. Heringer [EMAIL PROTECTED] Skype: ailsom.osklen Analista de Sistemas ---------------------------------- Osklen Departamento de Informática Rio de Janeiro - RJ BRASIL http://www.osklen.com.br 55 21 22198971 [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
