CODEWITHSUNDEEP
sqlsql-server-2005
HEEN
HEEN

Reputation: 4727

SQL count not showing result properly

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

Simple SELECT

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

JOIN QUERY

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

Upvotes: 0

Views: 588

Answers (2)

Deepak Bhatia
Deepak Bhatia

Reputation: 6276

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.

Upvotes: 1

Sandeep Kumar
Sandeep Kumar

Reputation: 1202

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

Upvotes: 1

Related Questions