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
