Count rows based on multiple columns

Question

So basically there are two tables.

First is location table -

id location_id city_name country_name
1  400212      Paris     Canada
2  400122      Paris     France
2  400122      Dubai     United Arab Emirates

Second is general_details table -

id hotel_id city_name country_name
1  2323     Dubai     United Arab Emirates
2  1231     Dubai     United Arab Emirates
3  2344     Paris     Canada
4  1218     Paris     France

So lets suppose I have two country_name and city_name pairs from table locations. I want to select number of rows for them each from table general details. Like I have Paris => Canada and Paris => France, so my result set should have 1, 1 i.e. their respective total records from table general_details. I am not sure how to do this. I can have multiple pairs from table 1 and I want all the counts from table 2.

Like I made an array of city and country -

array[0][city] = Paris, array[0][country] = France
array[1][city] = Paris, array[1][country] = Canada

Now I want output resultset after 2nd query to have count(*) as 1,1.

Kindly guide me here.

Is there a reason that `group by city_name, country_name` doesn't do what you want? — Gordon Linoff, Sep 16 '14 at 11:48
@GordonLinoff, I am not sure how to apply the where condition even if I use the groub by statement as you mentioned. — Anoop, Sep 16 '14 at 12:03

score 1 · Accepted Answer · answered Sep 16 '14 at 12:06

1

Try This:

 SELECT city_name,country_name, COUNT(*)
 FROM 
general_details 
WHERE 

city_name IN('Paris') AND country_name IN ('Canada' , 'France')

GROUP BY city_name,country_name

answered Sep 16 '14 at 12:06

Hansraj

174
5

Count rows based on multiple columns

1 Answers1