how to use count with where clause in join query

Question

    SELECT
           DEPTMST.DEPTID,
           DEPTMST.DEPTNAME,
           DEPTMST.CREATEDT,
           COUNT(USRMST.UID)             
    FROM DEPTMASTER  DEPTMST    
    INNER JOIN USERMASTER USRMST ON USRMST.DEPTID=DEPTMST.DEPTID    
    WHERE DEPTMST.CUSTID=1000 AND DEPTMST.STATUS='ACT

I have tried several combination but I keep getting error

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

I also add group by but it's not working

you need to use GROUP BY followed by all the column names which are not in aggregate functions eg. GROUP BY DEPTMST.DEPTID,DEPTMST.DEPTNAME ... — RobertKing
– RobertKing, Commented Nov 22, 2013 at 13:00
Is your code missing a string delimiter at the end? The number of columns in your GROUP BY statement required for your code to run is database engine specific - SQLServer requires all, MySQL requires one. Please add your RDBMS as a tag. However, so long as you are grouping by DEPTMST.DEPTID, DEPTMST.DEPTNAME, DEPTMST.CREATEDT it looks like it would work. — Steph Locke
– Steph Locke, Commented Nov 22, 2013 at 13:02

Chris L · Accepted Answer · 2013-11-22 13:01:13Z

1

WHen using count like that you need to group on the selected columns,

ie.

SELECT
    DEPTMST.DEPTID,
    DEPTMST.DEPTNAME,
    DEPTMST.CREATEDT,
    COUNT(USRMST.UID)             
    FROM DEPTMASTER  DEPTMST    
    INNER JOIN USERMASTER USRMST ON USRMST.DEPTID=DEPTMST.DEPTID    
    WHERE DEPTMST.CUSTID=1000 AND DEPTMST.STATUS='ACT'
GROUP BY DEPTMST.DEPTID,
           DEPTMST.DEPTNAME,
           DEPTMST.CREATEDT

answered Nov 22, 2013 at 13:01

Chris L

2,2921 gold badge19 silver badges34 bronze badges

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

Comments

vhadalgi · Accepted Answer · 2013-11-22 13:00:37Z

1

you miss group by

 SELECT     DEPTMST.DEPTID,
               DEPTMST.DEPTNAME,
               DEPTMST.CREATEDT,
               COUNT(USRMST.UID)             
        FROM DEPTMASTER  DEPTMST    
        INNER JOIN USERMASTER USRMST ON USRMST.DEPTID=DEPTMST.DEPTID    
        WHERE DEPTMST.CUSTID=1000 AND DEPTMST.STATUS='ACT

    group by DEPTMST.DEPTID,
               DEPTMST.DEPTNAME,
               DEPTMST.CREATEDT

answered Nov 22, 2013 at 13:00

vhadalgi

7,2376 gold badges44 silver badges67 bronze badges

Comments

Adarsh · Accepted Answer · 2013-11-22 13:09:29Z

0

All aggregate functions like averaging, counting,sum needs to be used along with a group by function. If you dont use a group by clause, you are performing the function on all the rows of the table.

Eg.

 Select count(*) from table;

This returns the count of all the rows in the table.

 Select count(*) from table group by name

This will first group the table data based on name and then return the count of each of these groups.

So in your case, if you want the countof USRMST.UID, group it by all the other columns in the select list.

answered Nov 22, 2013 at 13:09

Adarsh

3,6592 gold badges23 silver badges38 bronze badges

Collectives™ on Stack Overflow

how to use count with where clause in join query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related