CODEWITHSUNDEEP
sqloracle-databasegroup-bycount
AwesomeGuy
AwesomeGuy

Reputation: 547

SELECT COUNT if one of them fulfills condition

I have a table with: Country Varchar(20), City Varchar(20), Street Varchar(20)

I want to count the streets of all cities IF one of them is Main Street

It should be something like this...

SELECT Country, City, Count(Street)
FROM Streets
GROUP BY Country, City;

I don't know how to check if one of the street names is Main Street what should I do?

Upvotes: 0

Views: 81

Answers (3)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

You can use a subquery in the WHERE clause to find all the cities which have a Main Street:

SELECT COUNTRY, CITY, COUNT(STREETS)
  FROM STREETS
  WHERE (COUNTRY, CITY) IN (SELECT DISTINCT COUNTRY, CITY
                              FROM STREETS
                              WHERE UPPER(STREET) = 'MAIN STREET')
  GROUP BY COUNTRY, CITY

or you can use a join

SELECT s.COUNTRY, s.CITY, COUNT(s.STREETS)
  FROM STREETS s
  INNER JOIN (SELECT DISTINCT COUNTRY, CITY
                FROM STREETS
                WHERE LOWER(STREET) = 'main street') c
    ON c.COUNTRY = s.COUNTRY AND
       c.CITY = s.CITY

or you can use a Common Table Expression:

WITH cteCITIES_WITH_MAIN_STREET AS (SELECT DISTINCT COUNTRY, CITY
                                      FROM STREETS
                                      WHERE INITCAP(STREET) = 'Main Street')
SELECT s.COUNTRY, s.CITY, COUNT(s.STREETS)
  FROM STREETS s
  INNER JOIN cteCITIES_WITH_MAIN_STREET c
    ON c.COUNTRY = s.COUNTRY AND
       c.CITY = s.CITY

Note: not tested on animals; you'll be first! :-)

Best of luck.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can use having:

SELECT Country, City, Count(Street)
FROM Streets
GROUP BY Country, City
HAVING SUM(CASE WHEN Street = 'Main Street' THEN 1 ELSE 0 END) > 0;

Upvotes: 4

Sudarson Soundararajan
Sudarson Soundararajan

Reputation: 247

You can simply go with this.

SELECT Country, City, Count(Street) FROM Streets where upper(streets)='MAIN STREET' GROUP BY Country, City;

Upvotes: 0

Related Questions