SQL count not showing result properly

Question

I have a simple select query

select count(status_flag) STATUS_COUNT
from inward_doc_tracking_hdr
where to_user = 1279  and status_flag = 4

which displays results as

I have joined with user_mst table like below:

SELECT COUNT (a.status_flag) counts, a.mkey, a.to_user, b.email,
       b.first_name + ' ' + b.last_name name
FROM inward_doc_tracking_hdr a
    LEFT JOIN user_mst b ON a.to_user = b.mkey
WHERE a.to_user = '1279' AND a.status_flag = '4' 
group by a.mkey, a.to_user, b.email, b.first_name,b.last_name

which displays result as

So my issue is

why the second query is showing two rows for the same to_user whose count is 2.

I am using sql-server-2005

Because they belong in a different group since `mkey` is not equal. — Felix Pamittan, May 17 '16 at 05:13
So you need to remove `mkey` from your `GROUP BY`, if you want to combine and get the same `count` as previous query — sonique, May 17 '16 at 05:15
@sonique: yes removed and checked. Need to confirm with other people whether removing mkey is not an issue — Nad, May 17 '16 at 05:16

score 1 · Accepted Answer · answered May 17 '16 at 05:13

1

You are grouping on mkey also that's why its showing you two different counts. Use below query may be it helps you.

SELECT COUNT (a.status_flag) counts,  a.to_user, b.email, b.first_name + ' ' + 
b.last_name name FROM inward_doc_tracking_hdr a LEFT JOIN user_mst b 
ON a.to_user = b.mkey WHERE a.to_user = '1279' AND a.status_flag = '4' 
Group by  a.to_user, b.email, b.first_name,b.last_name

answered May 17 '16 at 05:13

Sandeep Kumar

1,172
1
9
22

hey sandeep, its working. let me confirm whether removing mkey wont make any issue. – Nad May 17 '16 at 05:20
If you have to use mkey then you can use its Max or MIN value also. What is your requirement ? – Sandeep Kumar May 17 '16 at 05:21

score 1 · Answer 2 · edited May 23 '17 at 12:23

I think you have not understood how group by on multiple keys work just read Using group by on multiple columns which will provide you information about it, and will definitely solve what problem is there in your SQL.

Moreover the problem you are getting two rows is because of column mkey which is different on both rows.

I am also unable to understand why, so many columns have been placed in group by as you have joined table on

LEFT JOIN user_mst b ON a.to_user = b.mkey

so you know exactly how rows are joining, (user is mapped with respective user data only), so I think only group by should be on a.to_user.

SQL count not showing result properly

2 Answers2