C# .NET - SQL SERVER - Asked By Kaviya on 19-Nov-11 02:15 AM

hi all i have a doubt 
this is my output i need to group same product (eg Product A that qty need to show 500) but it shows separetly. i used he query like this


select P.PRDNAME AS [Name],isnull(S.SALE_RATE,0) AS [Price], ISNULL( B.Qoh,0) as [Available Qty],
ISNULL(P.PRDDISPER,0) AS [Discount%], P.PARTNO AS BarCode,  
P.PRDID AS ID,ISNULL(O.RefPer1,0) AS [Tax%],SP.Sale_LstID as PRDSALEACID ,sum(ISNULL( B.Qoh,0)) as sumQty
from TRN_STKBAL as b 
inner join mst_product as p ON P.PRDID = B.PRDID 
inner join BR_SALERATE AS S ON P.PRDID=S.PRD_ID AND S.BR_ID='5'
inner join  Mst_ObjStateWisePrd AS SP ON SP.PID = P.PRDID  AND Sp.StateID =  '00100000008'  
inner join mst_ledger as o on O.LdgId=SP.Sale_LstID
WHERE p.cmpid=1 and b.GDNID = 5 group by P.PRDNAME,B.Qoh,B.GDNID ,
P.PRDDISPER,P.PARTNO,S.SALE_RATE,O.RefPer1,SP.Sale_LstID,p.prdid


Product A 50.00 100.000 0.00 00100000009 00100000013 5.00 00100000012 100.000
New Product 5250.00 100.000 0.00 232 00500000004 5.00 00100000012 100.000
Product A 50.00 400.000 0.00 00100000009 00100000013 5.00 00100000012 400.000
how to do..



Sunil Darji replied to Kaviya on 19-Nov-11 04:21 AM

  you have to use like below query  and run below query..

 
SELECT P.PRDNAME AS [Name]
     , sum(isnull(B.Qoh, 0)) AS sumQty
FROM
    TRN_STKBAL AS b
INNER JOIN mst_product AS p
    ON P.PRDID = B.PRDID
WHERE
    p.cmpid = 1
    AND b.GDNID = 5
GROUP BY
    P.PRDNAME
 

   it will result

   Product A 500.000
   New Product 100.000