Re: not a GROUP BY expression

From: Nicholas Carey <ncarey_at_speakeasy.org>
Date: Fri, 09 Nov 2001 04:35:42 -0000
Message-ID: <Xns9153D1819C8D6ncareyspeakeasyorg@207.126.101.92>

On 08 Nov 2001, kilmer_at_isomedia.com (Chris Kilmer) spake and said:

> I am trying to retrieve the results of a poll. I would like
> to display the choice, the number of votes the choice
> received and the percentage of the total votes the choice
> received. Here is the query:
>
> SELECT choice ,
> COUNT(choice) ,
> ( COUNT(choice) /
> ( SELECT COUNT(choice)
> FROM results
> WHERE pollid = 1
> )
> ) * 100
> FROM results
> WHERE pollid = 1
> GROUP BY choice
>
> When I run the query, I get the following error:
>
> SELECT choice, COUNT(choice), COUNT(choice)/(SELECT COUNT(*)
> *
> ERROR at line 1:
> ORA-00979: not a GROUP BY expression
>
> As you can see, the subquery is causing the problem. In the
> subquery, I'm trying to get the count of all the choices for
> the particular poll. I then want to divide the count of the
> current choice by the total number of choices. Any thoughts
> on how to get around this problem? Please respond to
> kilmer_at_isomedia.com as well as to the group. I sometimes
> have trouble with the newgroups. Thanks.

You could do it this way:

  select total.poll_id                  poll_id   ,
         vote.choice                    choice    ,
         vote.cnt                       votes     ,
         100.0 * vote.cnt / 
         total.votes                    votes_pct
  from ( select n.poll_id  poll_id ,
                n.choice   choice  ,
                count(n.*) cnt
         from vote n
         group by n.poll_id ,
                  n.choice
       ) vote ,
       ( select t.poll_id  poll_id ,
                count(t.*) votes
         from vote t
         group by t.poll_id
       ) total

  where n.poll_id = t.poll_id
  order by total.poll_id ,

           vote.choice

--

Received on Thu Nov 08 2001 - 22:35:42 CST