CODEWITHSUNDEEP
oracle11g
user2985600
user2985600

Reputation: 1

A query that contains a UNION of two SELECTS

A query that contains a UNION of two SELECTS that contain the columns LOCATION_ID,STREET_ADDRESS,CITY,COUNTRY_NAME and "No of Depts". The result must be a list of ALL locations with the number of departments in the location. The list must be ordered from the highest to the lowest number of departments.

SELECT locations.location_id, 
       locations.street_address, 
       locations.city, 
       locations.country_id 
FROM locations,departments
WHERE (locations.location_id = departments.location_id)
GROUP BY locations.location_id, 
         locations.street_address, 
         locations.city, 
         locations.country_id
UNION ALL
SELECT Count(departments.department_name) 
FROM departments
WHERE (locations.location_id = departments.location_id)
GROUP BY departments.department_id, departments.location_id
ORDER BY (departments.department_name) DESC;`

Upvotes: 0

Views: 55

Answers (1)

Hogan
Hogan

Reputation: 70528

Why doesn't this work?

  SELECT count() as c, 
         departments.department_name, 
         locations.location_id, 
         locations.street_address, 
         locations.city, 
         locations.country_id 
  FROM locations
  join departments ON locations.location_id = departments.location_id
  GROUP BY departments.department_name, locations.location_id, locations.street_address, locations.city, locations.country_id

Upvotes: 1

Related Questions