N K N K - 6 months ago 16
SQL Question

SQL query giving error on join

I want to run the below SQL query:

SELECT
COUNT (a.status_flag) counts, a.mkey, a.to_user
FROM inward_doc_tracking_hdr a
LEFT JOIN user_mst b ON a.to_user = b.mkey
WHERE a.to_user = 1260 AND a.status_flag = 4.


but it is not running, giving this error:


Column 'inward_doc_tracking_hdr.Mkey' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


What is wrong here?

I am using SQL-server-2005.

UPDATE

I have an issue, when I run simple

select count(status_flag) from inward_doc_tracking_hdr where to_user = 1279


I get 2 count.

But when I run

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


and it shows me 2 rows.

why ?

Answer

Whenever you select list has combination of aggregate and non aggregate columns you need to add Group by with non aggregate columns

SELECT COUNT (a.status_flag) counts, a.mkey, a.to_user
FROM inward_doc_tracking_hdr a
  LEFT JOIN user_mst b ON a.to_user = b.mkey
WHERE a.to_user = 1260 AND a.status_flag = 4
Group by a.mkey, a.to_user
Comments