-1

Im trying to display all the city names and their counts of events where the event price = 0

create table event
(eventID decimal(12) not null primary key, 
AddressID decimal(12) not null, 
eventName varchar(255) not null, 
Description varchar(255) not null, 
eventPrice decimal(10,2) not null,
eventDate date,
foreign key (addressID) references address(addressID));


create table city (
cityID decimal(12) not null primary key, 
cityName varchar(30) not null);

create table address ( 
addressID decimal(12) not null primary key, 
address varchar(100) not null, 
cityID decimal(12) not null, 
zipcode varchar(5)
foreign key (cityID) references city(CityID) );



insert into address values (103, '310 East Kingsbridge Road',204,'10458');
insert into city values (200,'Manhattan');
insert into City values (201,'Brooklyn');
insert into City values (202,'Queens');
insert into City values (203,'Staten Island');
insert into City values (204,'Bronx');

insert into event values
(3001,103,'Book Fair','Book fair is dedicated to engaging and growing the community of poets and writers',0,'2-may-2020');

Below select gives me the following output

select  cityName, count (eventPrice) as numberOfFreeEvents
from city
join address on address.cityId = city.cityID
left join event on event.addressID= address.addressID
group by cityName, eventPrice
having eventPrice = 0

Output:

cityName    |    numberOfFreeEvents

     Bronx  |    1

I'm trying to get the output to be

cityName           |numberOfFreeEvents

Bronx                   1

Manhattan               0

Staten Island           0

Queens                  0

Brooklyn                0
0

1 Answer 1

0

You want the EventPrice condition in the FROM clause, as it will filter out rows in the WHERE or HAVING.

Try this:

SELECT 
cityName,
COUNT(eventPrice) AS numberOfFreeEvents
FROM 
city
JOIN address
ON address.cityId = city.cityID
LEFT JOIN event
ON event.addressID = address.addressID
AND eventPrice = 0

GROUP BY 
cityName,
eventPrice

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.