CODEWITHSUNDEEP
sql
user3021739
user3021739

Reputation: 41

how to use count with where clause in join query

    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

Upvotes: 2

Views: 2485

Answers (3)

Adarsh
Adarsh

Reputation: 3641

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.

Upvotes: 0

Chris L
Chris L

Reputation: 2292

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

Upvotes: 1

vhadalgi
vhadalgi

Reputation: 7189

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

Upvotes: 1

Related Questions