CODEWITHSUNDEEP
mysql
AJFMEDIA
AJFMEDIA

Reputation: 2123

select count only showing 1 result and the wrong one

I want to search TABLE1 and count which number_id has the most 5's in experience column.

TABLE1

+-------------+------------+
| number_id   | experience |
+-------------+------------+
|  20         |  5         |
|  20         |  5         |
|  19         |  1         |
|  18         |  2         |
|  15         |  3         |
|  13         |  1         |
|  10         |  5         |
+-------------+------------+

So in this case it would be number_id=20

Then do an inner join on TABLE2 and map the number that matches the number_id in TABLE1.

TABLE2

+-------------+------------+
| id          | number     |
+-------------+------------+
|  20         |  000000000 |
|  29         |  012345678 |
|  19         |  123456789 |
|  18         |  223456789 |
|  15         |  345678910 |
|  13         |  123457898 |
|  10         |  545678910 |
+-------------+------------+

So the result would be:

000000000 (2 results of 5)
545678910 (1 result of 5)

So far I have:

SELECT number, experience, number_id, COUNT(*) AS SUM FROM TABLE1
INNER JOIN TABLE2 ON TABLE1.number_id = TABLE2.id
WHERE experience = '5' order by SUM LIMIT 10

But it's returning just

545678910

How can I get it to return both results and by order of number of instances of 5 in the experience column?

Thanks

Upvotes: 0

Views: 28

Answers (2)

Bohemian
Bohemian

Reputation: 425198

Add a group by clause:

SELECT number, experience, number_id, COUNT(*) AS SUM
FROM TABLE1
JOIN TABLE2 ON TABLE1.number_id = TABLE2.id
WHERE experience = '5'
GROUP BY 1, 2, 3 -- <<< Added this clause
ORDER BY SUM
LIMIT 10

Upvotes: 1

Nick
Nick

Reputation: 147206

This query will give you the results that you want. The subquery fetches all the number_id that have experience values of 5. The SUM(experience=5) works because MySQL uses a value of 1 for true and 0 for false. The results of the subquery are then joined to table2 to give the number field. Finally the results are ordered by the number of experience=5:

SELECT t2.number, t1.num_fives
FROM (SELECT number_id, SUM(experience = 5) AS num_fives
      FROM table1
      WHERE experience = 5
      GROUP BY number_id) t1
JOIN table2 t2
ON t2.id = t1.number_id
ORDER BY num_fives DESC

Output:

number      num_fives
000000000   2
545678910   1

SQLFiddle Demo

Upvotes: 1

Related Questions