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