CODEWITHSUNDEEP
mysqlsql
Ash
Ash

Reputation: 225

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

Upvotes: 1

Views: 6197

Answers (5)

Kickstart
Kickstart

Reputation: 21513

Just a note to explain the original query (the accepted solution is perfect to fix the issue).

|Month|Year|Total | 6|2016|3 | 7|2016|2 I tried using this sql script

You sub query is

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

This will return 2 rows:-

Total
3
2

You then cross join that with the wp_tsf_reports table, giving (temporarily):-

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

You haven't returned the month and year from the sub query, nor specified to join based on the month / year, hence the cross join which gives you every combination of rows from the table and from the sub query.

The GROUP BY is then picking the values (ie, the value of the total column) from 1 row for each month / year. Which rows value that MySQL picks to use is unspecified. It could be the 3 or the 2, or could even vary.

Hence you get:-

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

but equally you could get

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

Upvotes: 0

denny
denny

Reputation: 2254

simple query like this

select count(*),month,year  from report group by month having count(*) > 1;

the results

+----------+-------+------+
| count(*) | month | year |
+----------+-------+------+
|        3 |     6 | 2016 |
|        2 |     7 | 2016 |
+----------+-------+------+

2 rows in set (0.00 sec)

Upvotes: 0

priyanka
priyanka

Reputation: 36

You can try using below query, you will get the desired output.

SELECT 
month,
year,
count(*) as total 
FROM table_name 
where emp_id='5' 
group by month,year

Upvotes: 0

Rakesh Kumar
Rakesh Kumar

Reputation: 4420

Don't use any complex or sub query, this can achieved using single query

select month, year, count(1) total from wp_tsf_reports
where empid = 5
group by month,year

Upvotes: 2

Madhivanan
Madhivanan

Reputation: 13700

Use this

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

Upvotes: 6

Related Questions