CODEWITHSUNDEEP
sqlsql-server
deejay217
deejay217

Reputation: 109

SQL Server Query retrieves incorrect value

Below is transaction item table.

item

Now I wanted to retrieve total amount of the transaction, total amount with discount, total discount and Sales Tax for which I wrote the below query:

select t1.av_transaction_id,
    round(SUM((t1.gross_line_amount - t1.pos_discount_amount)), 2) AS total_amount_with_discount,
    round(SUM((t1.gross_line_amount)), 2) AS total_amount,
    round(SUM((t1.pos_discount_amount)), 2)    AS total_discount,
    round(SUM((t2.gross_line_amount)), 2)    AS total_sales_tax
from transaction_detail as t1 
inner join transaction_detail as t2 on     t1.av_transaction_id=t2.av_transaction_id
    and t1.transaction_date=t2.transaction_date
where (t1.sku_id is not null or t1.line_action_display_descr='sold')
    and t2.line_object_description='S6 Sales Tax'
    and t1.av_transaction_id='581280193'
group by t1.av_transaction_id

But I get the following output:

av_transaction_id:581280193 || total_amount_with_discount:5.01 || total_amount:6.67 || total_discount:1.66 || total_Sales_tax:0.66

As you may see in the screenshot the Sales Tax should be 0.22 but somehow the query returns 0.66.

Can someone pleas help me in optimizing this query and let me know why does it return an incorrect value ?

Upvotes: 2

Views: 215

Answers (3)

Gharbad The Weak
Gharbad The Weak

Reputation: 1641

I think that all you need to do is to not sum the sales tax, so your query would look like this:

select t1.av_transaction_id,
    round(SUM((t1.gross_line_amount - t1.pos_discount_amount)), 2) AS total_amount_with_discount,
    round(SUM((t1.gross_line_amount)), 2) AS total_amount,
    round(SUM((t1.pos_discount_amount)), 2)    AS total_discount,
    round(t2.gross_line_amount, 2)    AS total_sales_tax
from transaction_detail as t1 
inner join transaction_detail as t2 on     t1.av_transaction_id=t2.av_transaction_id
    and t1.transaction_date=t2.transaction_date
where (t1.sku_id is not null or t1.line_action_display_descr='sold')
    and t2.line_object_description='S6 Sales Tax'
    and t1.av_transaction_id='581280193'
group by t1.av_transaction_id

I mocked up your table with this script:

CREATE TABLE SO_TEST
(
    intTransID INT
    , objDesc VARCHAR(MAX)
    , displayDesc VARCHAR(MAX)
    , lintAmt FLOAT
    , discAmount FLOAT
)

INSERT INTO SO_TEST (intTransID, objDesc, displayDesc, lintAmt, discAmount)
VALUES
(1,'emp merch','sold',2.29,.57)
, (1,'emp merch','sold',1.89,.47)
, (1,'emp merch tax','sold',2.49,.62)
, (1,'sales tax','charged',.22,0.0)
, (1,'blah','blah',1.0,2.0)
, (2,'emp merch','sold',3.29,1.57)
, (2,'emp merch','sold',2.89,1.47)
, (2,'emp merch tax','sold',3.49,1.62)
, (2,'sales tax','charged',1.22,0.0)
, (2,'blah','blah',1.0,2.0)

And came up with this query that displays what I believe you want to have it display:

SELECT t1.intTransID
, ROUND(SUM(t1.lintAmt - t1.discAmount),2) tot_amt_disc
, ROUND(SUM(t1.lintAmt), 2) tot_amt
, ROUND(SUM(t1.discAmount),2) disc_amt
, ROUND(t2.lintAmt,2) sales_tax
FROM SO_TEST t1
JOIN SO_TEST t2 ON 1=1
    AND t1.intTransID = t2.intTransID
WHERE t1.displayDesc IN ('sold','charged')
    AND t1.objDesc <> 'sales tax'
    AND t2.objDesc = 'sales tax'
GROUP BY t1.intTransID, t2.lintAmt

The results of the query are as follows:

enter image description here

This works for multiple transactions as asked.

Upvotes: 0

deejay217
deejay217

Reputation: 109

This worked for me, thanks to previous respondents:

select t1.av_transaction_id,
round(SUM((t1.gross_line_amount - t1.pos_discount_amount)), 2)    AS    total_amount_with_discount,
round(SUM((t1.gross_line_amount)), 2)    AS total_amount,
round(SUM((t1.pos_discount_amount)), 2) AS total_discount,
round(SUM((t2.gross_line_amount))/count(t1.gross_line_amount), 2) AS total_sales_tax
  from transaction_detail as t1 inner join
    transaction_detail as t2 on t1.av_transaction_id=t2.av_transaction_id
and t1.transaction_date=t2.transaction_date
where (t1.sku_id is not null or t1.line_action_display_descr='sold')
and t2.line_object_description='S6 Sales Tax'
and t1.av_transaction_id='581280193'
group by t1.av_transaction_id

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 415790

There are three records for this transaction where the sku_id is not null or the display description is 'sold'. All three of those will match with the Sales Tax record for the join... and then you take the SUM(). So .22 + .22 + .22 = .66

To fix this, I'd use conditional aggregation rather than a self-join:

select t1.av_transaction_id,
    round(SUM(CASE WHEN t1.sku_id is not null or t1.line_action_display_descr='sold' THEN t1.gross_line_amount - t1.pos_discount_amount ELSE 0 END)), 2) AS total_amount_with_discount,
    round(SUM(CASE WHEN t1.sku_id is not null or t1.line_action_display_descr='sold' THEN t1.gross_line_amount ELSE 0 END), 2) AS total_amount,
    round(SUM(CASE WHEN t1.sku_id is not null or t1.line_action_display_descr='sold' THEN t1.pos_discount_amount ELSE 0 END), 2)    AS total_discount,
    round(SUM(CASE WHEN t1.line_object_description='S6 Sales Tax' THEN t1.gross_line_amount ELSE 0 END), 2)    AS total_sales_tax
from transaction_detail as t1 
where  t1.av_transaction_id='581280193'
group by t1.av_transaction_id

Though this repeats the same condition, so you might be able to wrap that up further to only resolve the condition once.

Upvotes: 1

Related Questions