CODEWITHSUNDEEP
sqlsql-server
jas backer
jas backer

Reputation: 199

take count of item depend on the specific calulation

I have a table like this:

enter image description here

i have a query like this: 

SELECT T_BOE_Stock_Detail.itemCode,
       T_Master_Item.itemName AS Description,
       T_Master_Item.categoryCode AS Category,
       sum(T_BOE_Stock_Detail.qty - T_BOE_Stock_Detail.ReservedQty) AS Qty
FROM T_BOE_Stock_Detail
LEFT OUTER JOIN T_Master_Item ON T_BOE_Stock_Detail.itemCode = T_Master_Item.itemCode
GROUP BY T_BOE_Stock_Detail.itemCode,
         T_Master_Item.itemName,
         T_Master_Item.categoryCode
HAVING sum(T_BOE_Stock_Detail.qty - T_BOE_Stock_Detail.ReservedQty) > 0
ORDER BY T_Master_Item.categoryCode,
         T_BOE_Stock_Detail.itemCode

in the query i am getting out put like this: 

itemcode   description   category     qty
076-00002  bottom                       2

in the same query i want to add two more column 1 is goodcount 2 is defectivecount ..
in the doctype column we have good and defective item.so i want to add count of that.

my expected out put like this:
 itemcode   description   category     qty  good   defective
  076-00002  bottom                    2       1      1

how i can achieve this?

Upvotes: 0

Views: 45

Answers (4)

Tirthak Shah
Tirthak Shah

Reputation: 515

I Updated ans but I was not able to test it due to not having test data, can you please try this

 Select T.itemCode,T.Description,T.Category,
 SUM(CASE WHEN DOCTYPE = 'GOOD' THEN 1 ELSE 0 END) Good,
 SUM(CASE WHEN DOCTYPE = 'DEFECTIVE' THEN 1 ELSE 0 END) Defective  
 from 
 (SELECT T_BOE_Stock_Detail.itemCode, T_Master_Item.itemName as Description,
       T_Master_Item.categoryCode as Category, 
 SUM(T_BOE_Stock_Detail.qty - T_BOE_Stock_Detail.ReservedQty) As Qty,
 FROM T_BOE_Stock_Detail 
 LEFT OUTER JOIN T_Master_Item ON T_BOE_Stock_Detail.itemCode = T_Master_Item.itemCode
 GROUP BY T_BOE_Stock_Detail.itemCode, T_Master_Item.itemName,T_Master_Item.categoryCode
 HAVING SUM(T_BOE_Stock_Detail.qty - T_BOE_Stock_Detail.ReservedQty) > 0 
 ORDER BY T_Master_Item.categoryCode, T_BOE_Stock_Detail.itemCode
 ) T
 inner join T_BOE_Stock_Detail ON T_BOE_Stock_Detail.itemCode = T.itemCode
 GROUP BY T.itemCode, T.Description,T.Category

Upvotes: 1

t-clausen.dk
t-clausen.dk

Reputation: 44326

Here is the full script, the performance has been improved a bit by aggregating before calculating:

SELECT BOE.itemCode,
       M.itemName AS Description,
       M.categoryCode AS Category,
       sum(BOE.qty) - sum(BOE.ReservedQty) AS Qty,
       count(case when DOCTYPE = 'GOOD' then 1 end) good,
       count(case when DOCTYPE = 'DEFECTIVE' then 1 end) defective
FROM T_BOE_Stock_Detail BOE
LEFT OUTER JOIN
  T_Master_Item M
ON BOE.itemCode = M.itemCode
GROUP BY BOE.itemCode,
         M.itemName,
         M.categoryCode
HAVING sum(BOE.qty) > sum(BOE.ReservedQty)
ORDER BY M.categoryCode,
         BOE.itemCode

Not sure how to prefix DOCTYPE, you should use M or BOE in my example

Upvotes: 1

Madhivanan
Madhivanan

Reputation: 13700

Try this

select t_boe_stock_detail.itemcode, t_master_item.itemname as description,
t_master_item.categorycode as category, 
sum(t_boe_stock_detail.qty - t_boe_stock_detail.reservedqty) as qty,
sum(case when doctype = 'good' then 1 end) as good,
sum(case when doctype = 'defective' then 1 end) as defective 
from t_boe_stock_detail 
left outer join t_master_item on t_boe_stock_detail.itemcode = t_master_item.itemcode
group by t_boe_stock_detail.itemcode, t_master_item.itemname,t_master_item.categorycode
having sum(t_boe_stock_detail.qty - t_boe_stock_detail.reservedqty) > 0 
order by t_master_item.categorycode, t_boe_stock_detail.itemcode

Upvotes: 1

Luaan
Luaan

Reputation: 63742

You can use case in the expression passed to sum. So you can for example do something like this:

sum(case when DOCTYPE = 'GOOD' then 1 else 0 end)

Upvotes: 3

Related Questions