Ash Ash - 4 months ago 8
SQL Question

MySQL select and count in same table

I have a table exactly like this

|month|year|candidate_id|emp_id
| 6|2016|10 |5
| 6|2016|11 |5
| 6|2016|12 |5
| 7|2016|13 |5
| 7|2016|14 |5


So I want to count how many candidates were added each month and display month, year and total candidates just like below

|Month|Year|Total
| 6|2016|3
| 7|2016|2


I tried using this sql script

SELECT
c.MONTH,
c.YEAR,
a.total
FROM
wp_tsf_reports c,
(
SELECT
COUNT(*) total
FROM wp_tsf_reports b
WHERE b.emp_id = '5'
GROUP BY b.MONTH, b.YEAR DESC
) a
WHERE
c.emp_id = '5'
GROUP BY c.MONTH, c.YEAR DESC


The output of this is

|Month|Year|Total
| 6|2016|3
| 7|2016|3


As you can see its showing 3 two times where total should be 3 for the 6th month and 2 for the 7th month.

Any help is appriciated

Answer

Use this

select month, year, count(*) as total from table
where empid=5
group by month,year
Comments