List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:July 25 2008 3:34pm
Subject:RE: How do I (can I) use aggregate functions inside a select
View as plain text  
>-----Original Message-----
>From: David Ruggles [mailto:david@stripped]
>Sent: Friday, July 25, 2008 10:53 AM
>To: 'mysql'
>Subject: RE: How do I (can I) use aggregate functions inside a select
>
>I get:
>Error Code : 1140
>Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns
>is
>illegal if there is no GROUP BY clause
>
[JS] I think you can do what you want with a little bit of modification, but
I'm not sure exactly what you want. If what you want is to have the sales %
for each company, for example, you could use

SELECT MIN(company) AS comp, MIN(state) AS st, SUM(sales) AS comp_tot,
  SUM(sales) * 100 / (SELECT SUM(sales) FROM sales_table)) AS pct_sales
FROM sales_table GROUP BY company, state;

I tested a simplified case, but I believe this will work. The use of the MIN
function is because you need a group function, and because the MIN and MAX
of company is the same through the entire group it works just fine.
>Thanks,
>
>David Ruggles
>CCNA MCSE (NT) CNA A+
>Network Engineer	Safe Data, Inc.
>(910) 285-7200	david@stripped
>
>
>
>-----Original Message-----
>From: Ananda Kumar [mailto:anandkl@stripped]
>Sent: Friday, July 25, 2008 10:37 AM
>To: David Ruggles
>Cc: mysql
>Subject: Re: How do I (can I) use aggregate functions inside a select
>
>
>yes, u can use the below sql.
>
>regards
>anandkl
>
>
>On 7/25/08, David Ruggles <david@stripped> wrote:
>>
>> I may be approaching this all wrong, but I need to know a percentage
>of
>> total sales within a select statement.
>>
>> So I can do something like this:
>> Select company, state, sales, sum(sales) / sales as percent
>> From Sales
>>
>>
>> Thanks,
>>
>> David Ruggles
>> CCNA MCSE (NT) CNA A+
>> Network Engineer        Safe Data, Inc.
>> (910) 285-7200  david@stripped
>>
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>http://lists.mysql.com/mysql?unsub=1
>>
>>
>No virus found in this incoming message.
>Checked by AVG - http://www.avg.com
>Version: 8.0.138 / Virus Database: 270.5.6/1572 - Release Date:
>7/25/2008
>6:51 AM
>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>infoshop.com




Thread
How do I (can I) use aggregate functions inside a selectDavid Ruggles25 Jul
  • Re: How do I (can I) use aggregate functions inside a selectAnanda Kumar25 Jul
    • RE: How do I (can I) use aggregate functions inside a selectDavid Ruggles25 Jul
      • RE: How do I (can I) use aggregate functions inside a selectIan Simpson25 Jul
      • RE: How do I (can I) use aggregate functions inside a selectJerry Schwartz25 Jul
  • Re: How do I (can I) use aggregate functions inside a selectwalter harms25 Jul