List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:May 9 2003 11:51pm
Subject:RE: sum command
View as plain text  
At 23:52 +0100 5/9/03, Andrew Braithwaite wrote:
>  >>select 'SPO', sum(plc_mac)as summac, sum(plc_dac) as sumdac where cvx
>=like"spo%"
>
>Don't forget to specify the table from which you would like to select as
>well ;)

Yes, I guess that would help.  Thanks.

Should I claim that's an exercise for the reader? :-)

>Cheers,
>
>Andrew
>
>-----Original Message-----
>From: Paul DuBois [mailto:paul@stripped]
>Sent: Friday 09 May 2003 01:52
>To: Daniel Kasak; Fabio Bernardo; Mysql (E-mail)
>Subject: Re: sum command
>
>
>At 9:31 +1000 5/9/03, Daniel Kasak wrote:
>>Fabio Bernardo wrote:
>>
>>>Hi there, I have a table like this...
>>>
>>>+------------------------------+---------+---------+-------------
>>>| cvx                         | plc_mac | plc_dac |
>>>+------------------------------+---------+---------+------------
>>>| BHEGTCCVX18-01 |      12 |       4 |      
>>>| BHEGTCCVX18-02 |      12 |       4
> |         
>>>| BHEGTCCVX18-03 |       6 |       3
> |        
>>>| CASMVZCVX18-01 |       1 |       1
> |        
>>>| RJOROCCVX18-05 |      12 |       4 |    
>>>| RJOROCCVX18-06 |       8 |       3 |     
>>>| SDRESUCVX18-01 |       5 |       3 |    
>>>| SPOLAPCVX18-01 |      10 |       4 |    
>>>| SPOLAPCVX18-02 |      11 |       4 |    
>>>| SPOLAPCVX18-03 |      10 |       4 |    
>>>| SPOLAPCVX18-04 |      11 |       4 |    
>>>| SPOLAPCVX18-05 |       4 |       2 |     
>>>+----------------+---------+---------+-------------+---
>>>And I would like to create a mysql command  which sum the plc_mac
>>>field and the plc_dac where cvx name starts with "SPO"
>>>
>>>I tried something like this but it didnt work
>>>
>>>select cvx, sum(plc_mac)as summac, sum(plc_dac) as sumdac where cvx
>>>=like"spo%" group by 1
>>>
>>>any tips??
>>>
>>>I wanna a result like this
>>>
>>>cvx                   sumac             sumdac
>>>SPO                     64                     24
>>>
>>>thanks a lot
>>>
>>>
>>You have to drop the equals sig, and change 'group by 1' to 'group by
>>cvx:
>>
>>select cvx, sum(plc_mac)as summac, sum(plc_dac) as sumdac where cvx
>>like 'spo%' group by cvx
>
>How are those different?
>
>The problem with the original query is that it groups by the values in the
>first column, which will generate separate sums for each distinct value that
>begins with "SPO".  Given that he wants only one group, there appears to be
>no need for a GROUP BY clause at all:
>
>select 'SPO', sum(plc_mac)as summac, sum(plc_dac) as sumdac where cvx
>=like"spo%"
>
>
>>
>>--
>>Daniel Kasak
>>IT Developer
>>* NUS Consulting Group*
>>Level 18, 168 Walker Street
>>North Sydney, NSW, Australia 2060
>>T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
>>email: dkasak@stripped
>  >website: www.nusconsulting.com


-- 
Paul DuBois
http://www.kitebird.com/
sql, query
Thread
sum commandFabio Bernardo9 May
  • Re: sum commandDaniel Kasak9 May
    • Re: sum commandPaul DuBois9 May
RE: sum commandAndrew Braithwaite9 May
RE: sum commandPaul DuBois11 May