question

Cyprian_Dzuda avatar image
Cyprian_Dzuda asked

How do i correct the following sql statement

Please have a look at the following query:

```

SELECT CS_Number,

Item_Num,

currency_code,

Stk_Code,

Stk_Desc,

sum(Stk_Qnty) AS CS_Stk_Quantity,

Selling_Price,

sum(Gross) AS Gross,

sum(Line_Tot) AS Line_Tot,

currency_code

FROM temp_cash_sale

WHERE cs_number = '01'

GROUP BY Stk_Code,

currency_code,

Selling_Price

ORDER BY Stk_Desc

```

The query is supposed to sum the (quantity sold, gross total, net total) and group by (stock_code, currency, price).


The issues here is the SQL query sometimes returns wrong results.


For example: Input



+-----------+-------------+---------------+----------+----------+----------+---------------+----------+

| cs_number | item_number | currency_code | stk_code | Stk_Desc | Stk_Qnty | Selling_Price | Line_Tot |

+-----------+-------------+---------------+----------+----------+----------+---------------+----------+

| 01 | 1 | USD | A001 | MILK | 2 | 5 | 10 |

| 01 | 2 | USD | A001 | MILK | 3 | 5 | 15 |

| 01 | 3 | USD | A001 | MILK | 1 | 4.5 | 4.5 |

| 01 | 4 | ZWL | A001 | MILK | 2 | 50 | 100 |

+-----------+-------------+---------------+----------+----------+----------+---------------+----------+


Expected output


+-----------+-------------+---------------+----------+----------+-----------------+---------------+----------+

| cs_number | item_number | currency_code | stk_code | Stk_Desc | CS_Stk_Quantity | Selling_Price | Line_Tot |

+-----------+-------------+---------------+----------+----------+-----------------+---------------+----------+

| 01 | 2 | USD | A001 | MILK | 5 | 5 | 25 |

| 01 | 3 | USD | A001 | MILK | 1 | 4.5 | 4.5 |

| 01 | 4 | ZWL | A001 | MILK | 2 | 50 | 100 |

+-----------+-------------+---------------+----------+----------+-----------------+---------------+----------+


If the above sql statement is wrong. Please assist me with the correct one

sql query
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.