Group by with count not working Mysql Php

Question

I have two tables "employer" and "user", and i want to get total user(according to country) Where "skills" and "entity" column matches (like query) with both table

Table employer

id        skills        entity
1         Php,Design    web

Table user

id       skills                     entity          country
33       programming,php            web             india
44       Graphic designing,php      design          canada
45       Php                        web             india       
46       Dot net                    test            Sri Lanka

Now i want to get result like

id      Count       country 
1       2           india
2       1           canada

i am trying with following query but not working for me, where i am wrong ?

SELECT employer_info.id as employer_id,users_info.country,COUNT(users_info.country) as total 
   FROM employer_info 
   JOIN users_info ON employer_info.skills=users_info.skills 
   GROUP BY users_info.country

Welcome Priya to stackoverflow. You cannot use `employer_info.id`. Remove it and run query again! Read more for [Group By](http://www.mysqltutorial.org/mysql-group-by.aspx) — x01saa, Mar 30 '19 at 08:04
That's true. There is no common value in skills' column. It seems that you must change your schema (change skills from comma-separated to single skill in each row) or query entirely. — x01saa, Mar 30 '19 at 08:13

score 0 · Answer 1 · answered Mar 30 '19 at 08:07

0

If you want to count the number of users in a country by employer, you should count the number of user ids and not the country.

As for the GROUP BY, this should be done on the non-aggregated fields, which are the employer_info.id and users_info.country

SELECT employer_info.id as employer_id,users_info.country,
           COUNT(users_info.id) as total 
       FROM employer_info 
       JOIN users_info 
             ON employer_info.skills=users_info.skills 
       GROUP BY employer_info.id, users_info.country

answered Mar 30 '19 at 08:07

Nigel Ren

56,122
11
43
55

showing wrong result, showing me following result employer_id country total 1 null 1 2 null 1 7 null 1 – amit Mar 30 '19 at 08:12
How are you running this query? – Nigel Ren Mar 30 '19 at 08:13
If your employer table contains `Php,Design`, then this must also be the exact same value in the users_info table. It won't match `programming,php` or even `PHP` – Nigel Ren Mar 30 '19 at 08:16
understand but can we use mysql like query with this ? and my second question is if i use same value ( both table value matches ) then also query not working, i tested it properly – amit Mar 30 '19 at 08:18
If you want a list of skills, you should be using a separate table, comma separated fields in a relational database isn't a good idea - https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – Nigel Ren Mar 30 '19 at 09:20

score 0 · Answer 2 · edited Mar 30 '19 at 08:33

0

Try this one:

SELECT A.id as employer_id,B.country,COUNT(B.country) as total FROM employer_info A JOIN users_info B ON A.skills=B.skills AND A.entity=B.entity GROUP BY A.id, B.country

edited Mar 30 '19 at 08:33

x01saa

460
1
8
18

answered Mar 30 '19 at 08:17

KRISHNA KANT SINGH

77
1
5

Group by with count not working Mysql Php

2 Answers2