CODEWITHSUNDEEP
sqlsql-server
SQL User
SQL User

Reputation: 61

How to get multiple counts in one sql query across multiple tables

I have 2 tables

Company & products

I need to get 2 counts. One is the total count of products and the secondly count of products for sale_flg=1

This SQL does not seem to work..Tried several other ways..not able to get the expected results

SELECT A.COMPANY_NAME, COUNT(B.PRODUCT_ID) AS TOTAL_COUNT_OF_PRODUCTS, 
       (CASE WHEN B.SALEFLG =1 THEN 1 END) AS COUNT_OF_SALES
FROM COMPANY A LEFT JOIN
     PRODUCT B
     ON B.COMPANY_ID = A.COMPANY_ID
GROUP BY A.COMPANY_NAME

Upvotes: 0

Views: 70

Answers (4)

ITSGuru
ITSGuru

Reputation: 194

This SQL server query is working. Company Table and Product Table Company Table CompanyID is join with Product table and sales_flg add in product table .

sales_flg = 1 record display in CntSalesflg

select Comp.CompID as CompID, COUNT(Pro.ProductID) as CntProdustID, 
SUM(CASE WHEN Pro.SalesflagID = 1 THEN 1 ELSE 0 END) as CntSalesflg
from Product as Pro
inner join Company as Comp on Pro.CompID = Comp.CompID
GROUP by Comp.CompID

Upvotes: 0

Fahmi
Fahmi

Reputation: 37493

If you use count then in else you should consider null because null is not consider in count aggregation and if you have B.SALEFLG =1 or 0 then use sum aggregation.

You can try below code:

SELECT A.COMPANY_NAME, COUNT(B.PRODUCT_ID) AS TOTAL_COUNT_OF_PRODUCTS, 
       count(CASE WHEN B.SALEFLG =1 THEN 1 else null END) AS COUNT_OF_SALES
FROM COMPANY A LEFT JOIN
     PRODUCT B
     ON B.COMPANY_ID = A.COMPANY_ID
GROUP BY A.COMPANY_NAME

OR try this:

SELECT A.COMPANY_NAME, COUNT(B.PRODUCT_ID) AS TOTAL_COUNT_OF_PRODUCTS, 
       sum(B.SALEFLG) AS COUNT_OF_SALES
FROM COMPANY A LEFT JOIN
     PRODUCT B
     ON B.COMPANY_ID = A.COMPANY_ID
GROUP BY A.COMPANY_NAME

Upvotes: 0

Victor
Victor

Reputation: 182

If you have B.SALEFLG = 1 or 0 for you may try 

Sum(B.SALEFLG) AS COUNT_OF_SALES

Or use UNION

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271111

I think you just need a sum for the case:

SELECT C.COMPANY_NAME, COUNT(P.PRODUCT_ID) AS TOTAL_COUNT_OF_PRODUCTS, 
       SUM(CASE WHEN P.SALEFLG = 1 THEN 1 ELSE 0 END) AS COUNT_OF_SALES
FROM COMPANY C LEFT JOIN
     PRODUCT P
     ON P.COMPANY_ID = C.COMPANY_ID
GROUP BY C.COMPANY_NAME ;

Upvotes: 1

Related Questions