Ailsom, Having understood what you meant I would have come up with the subquery solution as well. Give the DBA a sleepless night!
Dave Crozier -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ailsom F. Heringer (Osklen) Sent: 09 March 2007 14:50 To: [email protected] Subject: Re: SUM + GROUP BY ??? Dave, Sorry. I have learned English from Arnold Schwarzenegger movies. It can cause some confusion, sometimes . Let me try again: I cannot use "Item_total" because it has wrong values, and not because the NULL values. It SHOULD NOT have any null value. So I have to get only one "Ticket_Total" for each "Shop"+"Ticket", to have a total for "Shop" . I have decided to use the sub-query. The result is ok. But it has a bad performance. I will ask our DBA to help me. (It´s SQLSERVER) Aílsom Dave Crozier escreveu: > 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 [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.
