MySQL select and count in same table

Question

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

select month, year, count(1) as total_count from where empid=5 table group by month,year; — JYoThI
– JYoThI, Commented Jul 21, 2016 at 10:02

Madhivanan · Accepted Answer · 2016-07-21 10:02:17Z

6

Use this

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

answered Jul 21, 2016 at 10:02

Madhivanan

13.7k1 gold badge28 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Saravana Over a year ago

Woks as expected.

Rakesh Kumar · Accepted Answer · 2016-07-21 10:04:09Z

2

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

answered Jul 21, 2016 at 10:04

Rakesh Kumar

4,4522 gold badges19 silver badges30 bronze badges

Comments

1000111 · Accepted Answer · 2016-07-21 10:22:46Z

0

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

edited Jul 21, 2016 at 10:22

1000111

13.5k2 gold badges32 silver badges43 bronze badges

answered Jul 21, 2016 at 10:19

priyanka

366 bronze badges

Comments

denny · Accepted Answer · 2016-07-21 10:23:38Z

0

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)

answered Jul 21, 2016 at 10:23

denny

2,2942 gold badges18 silver badges21 bronze badges

Comments

Kickstart · Accepted Answer · 2016-07-25 10:35:04Z

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

Collectives™ on Stack Overflow

MySQL select and count in same table

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related