CODEWITHSUNDEEP
sqlsql-server
weskpga
weskpga

Reputation: 2087

Aggregate function within table I'm creating

I have a dataset where I have an ItemID and then quantity sold at each price like this:

ItemID  |  Quantity  |  Price
ABC        10           14.50
ABC        4            14.25
DEF        32           22.41
ABC        24           14.10
GHI        8            8.50
GHI        12           8.60
DEF        2            22.30

Every entry has a unique combination of ItemID and Price. I would like to add a fourth column that has the total quantity sold for that ItemID. So it would look like this for the above table:

ItemID  |  Quantity  |  Price  |  TotalQ
ABC        10           14.50     38
ABC        4            14.25     38
DEF        32           22.41     34
ABC        24           14.10     38
GHI        8            8.50      20
GHI        12           8.60      20
DEF        2            22.30     34

I can't seem to do this without performing an aggregate function on an aggregate function, which obviously gives an error. How would I go about accomplishing this?

I'm using SQL Server 2008.

Upvotes: 0

Views: 1211

Answers (3)

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

If you need this just in query you can write something like this:

;WITH CTE_Total AS
(
    SELECT ItemID, SUM(Quantity) as TotalQ
    FROM YourTable
    GROUP BY ItemID
)
SELECT t.*, c.TotalQ
FROM YourTable t
LEFT JOIN CTE_Total c on t.ItemID = c.ItemID

SQLFiddle DEMO

But, if you want to have automated computed column in your table, first you need to create function that does the calculation:

CREATE FUNCTION dbo.fn_YourTableTotalQ (@ItemID VARCHAR(3))
RETURNS Int
AS
BEGIN
   DECLARE @toRet INT

   SELECT @toRet = COALESCE(SUM(Quantity),0)
   FROM YourTable WHERE ItemID = @ItemID

   RETURN @toRet
END

... and then add your new column as computed using this function:

ALTER TABLE YourTable
ADD TotalQ AS dbo.fn_YourTableTotalQ(ItemID)

SQLFiddle DEMO

Upvotes: 0

Priscilla Jobin
Priscilla Jobin

Reputation: 607

Try this code

select a.ItemID,a.Quantity,a.Price,x.Total form table_name a
left outer join 
(select sum(Quantity) Total, ItemID from table_name group by ItemID)x
on x.ItemID = a.ItemID

Upvotes: 0

TechDo
TechDo

Reputation: 18659

Please try:

SELECT 
    *, 
    SUM(Quantity) OVER(PARTITION BY ItemID) TotalQ
FROM 
    YourTable

Upvotes: 4

Related Questions