CODEWITHSUNDEEP
sql-server
Jun Rikson
Jun Rikson

Reputation: 1884

Get Discount from Global Discount in SQL SERVER

I've been stuck here for almost 4 hours. There is Global Discount in my invoice_header table. And I wanna count discount for every items in my invoice_detail proportion from value in invoice_detail and in the end and grouping by item_code.

The problem is I can't sum value in invoice_detail :

invoice_header : id, global_discount

invoice_detail : id, invoice_header_id, stock_code, value

SELECT SUM(CASE WHEN A.global_discount <> 0  
                THEN ((B.value * A.global_discount /
                    (SELECT SUM(value) FROM invoice_detail WHERE invoice_header_id = A.id)
                    )) 
                ELSE 0 
                END)/1.1
            AS DISCOUNT
        FROM invoice_header A 
            JOIN invoice_detail B
        ON A.id = B.invoice_header_id
        GROUP BY stock_code

I got error like this :

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

I've cut some code. The formula is Discount = value * global_discount / sum(value)

Upvotes: 0

Views: 68

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93704

Remove the Sum and make the query as Sub-select and do the Sum in outer Query.

Also you don't need Case part, when A.global_discount is 0 then Discount will be 0. Try this.

SELECT Sum(INT_DISCOUNT) / 1.1 AS Discount,stock_code
FROM   (SELECT stock_code,B.value * A.global_discount / (SELECT Sum(value)
                                              FROM   invoice_detail
                                              WHERE  invoice_header_id = A.id) INT_DISCOUNT
        FROM   invoice_header A
               JOIN invoice_detail B
                 ON A.id = B.invoice_header_id
        ) A 
 Group by stock_code

Upvotes: 1

Sarath Subramanian
Sarath Subramanian

Reputation: 21281

Try this

;WITH CTE AS
(        
    SELECT B.*,A.global_discount,SUM(B.value) OVER(PARTITION BY B.invoice_header_id) SUMM
    FROM invoice_header A 
    JOIN invoice_detail B
    ON A.id = B.invoice_header_id
)
SELECT stock_code,SUM(CASE WHEN global_discount = 0 THEN 0 ELSE (VALUE * global_discount)/SUMM END)/1.1 SUMMMM
FROM CTE
GROUP BY stock_code

Upvotes: 0

Related Questions