MySQL SUM on two columns

--00163642753f06e9e7048f1c3dfe
Content-Type: text/plain; charset=ISO-8859-1

Hi,

I've the following basic SUM for our products based on a rating.

SELECT SUM(products.rating) AS products_rating
FROM products_sales
INNER JOIN products ON products_sales.products_id = products.products_id
WHERE products.enabled = 1
AND products_sales.language = 'EN'

This works fine, however I also want to SUM the ES language within the same
query. Is this possible to do, or should I just query the table twice,
replacing the language parameter ?

Thanks.

Neil

--00163642753f06e9e7048f1c3dfe--

RE: MySQL SUM on two columns

I don't think that SUM will work for varchar columns.
If I got your point right - you need to use WITH ROLLUP grouping
http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.ht ml

see second example on that page.

regards,
m

-----Original Message-----
From: Tompkins Neil [mailto:neil.tompkins@googlemail.com]
Sent: Tuesday, August 31, 2010 11:43 AM
To: [MySQL]
Subject: MySQL SUM on two columns

Hi,

I've the following basic SUM for our products based on a rating.

SELECT SUM(products.rating) AS products_rating
FROM products_sales
INNER JOIN products ON products_sales.products_id = products.products_id
WHERE products.enabled = 1
AND products_sales.language = 'EN'

This works fine, however I also want to SUM the ES language within the same
query. Is this possible to do, or should I just query the table twice,
replacing the language parameter ?

Thanks.

Neil


------------------------------------------------------------ ----------
Saloon gier - to nas wyroznia!
Sprawdz >>> http://linkint.pl/f27e0


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: MySQL SUM on two columns

--00c09fa2185fb40aad048f1c584a
Content-Type: text/plain; charset=ISO-8859-1

Sorry I don't want to SUM the varchar columns. This is the parameter passed
that defines the two different queries.



On Tue, Aug 31, 2010 at 11:48 AM, misiaQ wrote:

> I don't think that SUM will work for varchar columns.
> If I got your point right - you need to use WITH ROLLUP grouping
> http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.ht ml
>
> see second example on that page.
>
> regards,
> m
>
> -----Original Message-----
> From: Tompkins Neil [mailto:neil.tompkins@googlemail.com]
> Sent: Tuesday, August 31, 2010 11:43 AM
> To: [MySQL]
> Subject: MySQL SUM on two columns
>
> Hi,
>
> I've the following basic SUM for our products based on a rating.
>
> SELECT SUM(products.rating) AS products_rating
> FROM products_sales
> INNER JOIN products ON products_sales.products_id = products.products_id
> WHERE products.enabled = 1
> AND products_sales.language = 'EN'
>
> This works fine, however I also want to SUM the ES language within the same
> query. Is this possible to do, or should I just query the table twice,
> replacing the language parameter ?
>
> Thanks.
>
> Neil
>
>
> ------------------------------------------------------------ ----------
> Saloon gier - to nas wyroznia!
> Sprawdz >>> http://linkint.pl/f27e0
>
>

--00c09fa2185fb40aad048f1c584a--

Re: MySQL SUM on two columns

--0016363b84d20ba8bf048f1c9328
Content-Type: text/plain; charset=ISO-8859-1

SELECT products_sales.language, SUM(products.rating) AS products_rating
FROM products_sales
INNER JOIN products ON products_sales.products_id = products.products_id
WHERE products.enabled = 1
AND products_sales.language IN ('EN','ES')
GROUP BY products_sales.language


Will give the sum for each language.

On 31 August 2010 11:42, Tompkins Neil wrote:

> Hi,
>
> I've the following basic SUM for our products based on a rating.
>
> SELECT SUM(products.rating) AS products_rating
> FROM products_sales
> INNER JOIN products ON products_sales.products_id = products.products_id
> WHERE products.enabled = 1
> AND products_sales.language = 'EN'
>
> This works fine, however I also want to SUM the ES language within the same
> query. Is this possible to do, or should I just query the table twice,
> replacing the language parameter ?
>
> Thanks.
>
> Neil
>



--
John Daisley

Certified MySQL 5 Database Administrator
Certified MySQL 5 Developer
Cognos BI Developer

Telephone: +44 (0)7918 621621
Email: john.daisley@butterflysystems.co.uk

--0016363b84d20ba8bf048f1c9328--

Re: MySQL SUM on two columns

--00c09f9c9cf0c4689a048f1ca557
Content-Type: text/plain; charset=ISO-8859-1

Perfect. Exactly what I was looking for.

Cheers John.

Regards,
Neil

On Tue, Aug 31, 2010 at 12:06 PM, John Daisley
wrote:

> SELECT products_sales.language, SUM(products.rating) AS products_rating
>
> FROM products_sales
> INNER JOIN products ON products_sales.products_id = products.products_id
> WHERE products.enabled = 1
> AND products_sales.language IN ('EN','ES')
> GROUP BY products_sales.language
>
>
> Will give the sum for each language.
>
> On 31 August 2010 11:42, Tompkins Neil wrote:
>
>> Hi,
>>
>> I've the following basic SUM for our products based on a rating.
>>
>> SELECT SUM(products.rating) AS products_rating
>> FROM products_sales
>> INNER JOIN products ON products_sales.products_id = products.products_id
>> WHERE products.enabled = 1
>> AND products_sales.language = 'EN'
>>
>> This works fine, however I also want to SUM the ES language within the
>> same
>> query. Is this possible to do, or should I just query the table twice,
>> replacing the language parameter ?
>>
>> Thanks.
>>
>> Neil
>>
>
>
>
> --
> John Daisley
>
> Certified MySQL 5 Database Administrator
> Certified MySQL 5 Developer
> Cognos BI Developer
>
> Telephone: +44 (0)7918 621621
> Email: john.daisley@butterflysystems.co.uk
>

--00c09f9c9cf0c4689a048f1ca557--

Re: MySQL SUM on two columns

--0015175cb568caa675048f1d6bc2
Content-Type: text/plain; charset=ISO-8859-1

Hi,

I also wondered, if there is any way to LIMIT the products.rating for each
language to say the top 25 ?

Cheers
Neil

On Tue, Aug 31, 2010 at 12:11 PM, Tompkins Neil <
neil.tompkins@googlemail.com> wrote:

> Perfect. Exactly what I was looking for.
>
> Cheers John.
>
> Regards,
> Neil
>
> On Tue, Aug 31, 2010 at 12:06 PM, John Daisley > > wrote:
>
>> SELECT products_sales.language, SUM(products.rating) AS products_rating
>>
>> FROM products_sales
>> INNER JOIN products ON products_sales.products_id = products.products_id
>> WHERE products.enabled = 1
>> AND products_sales.language IN ('EN','ES')
>> GROUP BY products_sales.language
>>
>>
>> Will give the sum for each language.
>>
>> On 31 August 2010 11:42, Tompkins Neil wrote:
>>
>>> Hi,
>>>
>>> I've the following basic SUM for our products based on a rating.
>>>
>>> SELECT SUM(products.rating) AS products_rating
>>> FROM products_sales
>>> INNER JOIN products ON products_sales.products_id = products.products_id
>>> WHERE products.enabled = 1
>>> AND products_sales.language = 'EN'
>>>
>>> This works fine, however I also want to SUM the ES language within the
>>> same
>>> query. Is this possible to do, or should I just query the table twice,
>>> replacing the language parameter ?
>>>
>>> Thanks.
>>>
>>> Neil
>>>
>>
>>
>>
>> --
>> John Daisley
>>
>> Certified MySQL 5 Database Administrator
>> Certified MySQL 5 Developer
>> Cognos BI Developer
>>
>> Telephone: +44 (0)7918 621621
>> Email: john.daisley@butterflysystems.co.uk
>>
>
>

--0015175cb568caa675048f1d6bc2--