List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:May 9 2003 12:51am
Subject:Re: sum command
View as plain text  
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