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 _______________________________________________ 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.
