I have this table
and I made a query to collect the totals :
SELECT
SaleID,
UserID,
SUM(SaleQuantity) AS TOTALQNTY,
SUM(SaleFinalPrice * SaleQuantity) AS FINALPRICE
FROM
xxxtemp
GROUP BY
UserID;
What I want is to collect the totals when
SaleInOut = ‘in’
and when
SaleInOut = ‘out’
I tried this query :
SELECT
SaleID,
UserID,
CASE WHEN SaleInOut = 'in' THEN SUM(SaleQuantity)
END AS TOTALQNTYIN,
CASE WHEN SaleInOut = 'in' THEN SUM(SaleFinalPrice * SaleQuantity)
END AS FINALPRICEIN,
CASE WHEN SaleInOut = 'out' THEN SUM(SaleQuantity)
END AS TOTALQNTYOUT,
CASE WHEN SaleInOut = 'out' THEN SUM(SaleFinalPrice * SaleQuantity)
END AS FINALPRICEOUT
FROM
xxxtemp
GROUP BY
UserID;
and the result :
Any help or suggestions will be appreciated…
Thank you in advance
Regards,
3
Change your query as follows:
SELECT
SaleID,
UserID,
SUM(CASE WHEN SaleInOut = 'in' THEN SaleQuantity END) AS TOTALQNTYIN,
SUM(CASE WHEN SaleInOut = 'out' THEN SaleQuantity END) AS TOTALQNTYOUT,
SUM(CASE WHEN SaleInOut = 'in' THEN (SaleFinalPrice * SaleQuantity) END) AS FINALPRICEIN,
SUM(CASE WHEN SaleInOut = 'out' THEN (SaleFinalPrice * SaleQuantity) END) AS FINALPRICEOUT
FROM
xxxtemp
GROUP BY
UserID;
Thank You
1