CODEWITHSUNDEEP
sqloracle-database
stakahop
stakahop

Reputation: 951

SQL Group By Function

I need to filter data about state that contains more than one location. My code is:

SELECT
  cc.country_id,
  cc.country_name,
  l.city,
  l.street_address,
  l.postal_code,
  l.state_province,
  count(*)
  FROM locations l, countries cc
 WHERE l.country_id = cc.country_id
 ORDER BY cc.country_id
 GROUP BY (cc.country_id, cc.country_name)
HAVING count(*) > 1;

And i get a following message:

ORA-00937: not a single-group group function
00937. 00000 -  "not a single-group group function"
*Cause:    
*Action:
Error at Line: 6 Column: 9

What is the problem?

Upvotes: 0

Views: 189

Answers (3)

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

All the fields which are not in an aggregation function (count, sum, avg, etc.) must be in the group by clause

so there, you should have

group by cc.country_id, cc.country_name, 
         l.city, l.street_address, l.postal_code, l.state_province

or you should remove these fields from select clause, or put them in an aggregation function in the select clause, or change your query.

by the way, it would be better to use the join syntax

replacing

FROM locations l, countries cc
 where l.country_id = cc.country_id

by

FROM location l
inner join countries cc on l.country_id = cc.country_id

and finally, ORDER BY must be the last statement in a query

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269803

There are several issues with the query:

  1. The group by needs all the fields
  2. The order by is in the wrong place
  3. It uses implicit rather than explicit join syntax

The following should work:

 SELECT cc.country_id, cc.country_name, l.city, l.street_address,
        l.postal_code, l.state_province, count (*)
 FROM locations l join
      countries cc
      on l.country_id = cc.country_id
 group by cc.country_id, cc.country_name, l.city, l.street_address,
          l.postal_code, l.state_province
 having count(*) > 1
 order by cc.country_id;

However, if you want to count rows within a country, then remove the additional fields:

 SELECT cc.country_id, cc.country_name, count (*)
 FROM locations l join
      countries cc
      on l.country_id = cc.country_id
 group by cc.country_id, cc.country_name
 having count(*) > 1
 order by cc.country_id;

Upvotes: 1

peterm
peterm

Reputation: 92785

You can leverage windowing functions 

SELECT c.country_id, c.country_name, l.city, l.street_address, l.postal_code, l.state_province, l.l_count 
  FROM countries c JOIN 
(
  SELECT country_id, city, street_address, postal_code, state_province,
         COUNT(*) OVER (PARTITION BY country_id) l_count
    FROM locations
) l
    ON l.country_id = c.country_id
 WHERE l.l_count > 1
 ORDER BY c.country_id

Here is SQLFiddle demo

Upvotes: 0

Related Questions