CODEWITHSUNDEEP
mysql
cheena
cheena

Reputation: 69

How do I include multiple conditions for MySql query?

I am trying to count the number of clinics in each zipcode for my table. Using the query below, this counts the number of clinics but also includes repetitions. E.g Clinic A is shown to appear in 5 rows because there is a column that shows the days for each clinic. To make this easy to understand, please look at a snapshot of my table: sql table So you see there are some addresses that appear only on one day and some appear more than once. How do I write my query to account for that? I tried addresses but it gave me a syntax error. Here is what I tried but I know its still adding repeated clinics:

SELECT SUBSTRING_INDEX(Date, "/", -1) As `Year`, `ZIP Code`, COUNT(`ZIP Code`) AS `Facilities` FROM `past_chicago_clinics` GROUP BY `ZIP Code`, `Year`

The result of the query is shown below:countimage

Upvotes: 0

Views: 36

Answers (1)

cheena
cheena

Reputation: 69

The word 'distinct' just came to me. I simply edited my code to:

SELECT SUBSTRING_INDEX(Date, "/", -1) As `Year`, `ZIP Code`, COUNT(DISTINCT `Address`) AS `Facilities` FROM `past_chicago_clinics` GROUP BY `ZIP Code`, `Year`

UpdatedCount

This shows the number of unique addresses and thus, the number of clinics based on the address.

Upvotes: 1

Related Questions