SQLTeam.com | Weblogs | Forums

Mathematical Operation Problem with SQL Code

I share the SQL code of a certain part of my project with friends.

I want to do this.

I want to divide the value by the number of pieces from the Total Column and show the result in a different column.

SELECT
dbo.TBL_SAFE_MOVEMENT.PIECE,
(CASE WHEN dbo.TBL_SAFE_MOVEMENT.GCKODU = 'G' THEN NETAMOUNT ELSE 0 END) AS DEBIT,
(CASE WHEN dbo.TBL_SAFE_MOVEMENT.GCKODU = 'C' THEN NETAMOUNT ELSE 0 END) AS CREDIT,
(CASE WHEN dbo.TBL_SAFE_MOVEMENT.GCKODU = 'G' THEN NETAMOUNT ELSE 0 END) - (CASE WHEN dbo.TBL_SAFE_MOVEMENT.GCKODU = 'C' THEN NETAMOUNT ELSE 0 END) AS XYZ,
dbo.TBL_SAFE_MOVEMENT.DOWNPAYMENT,
(CASE WHEN dbo.TBL_SAFE_MOVEMENT.GCKODU = 'G' THEN NETAMOUNT ELSE 0 END) - (CASE WHEN dbo.TBL_SAFE_MOVEMENT.GCKODU = 'C' THEN NETAMOUNT ELSE 0 END) - dbo.TBL_SAFE_MOVEMENT.DOWNPAYMENT AS TOTALSUM,
(CASE WHEN dbo.TBL_SAFE_MOVEMENT.GCKODU = 'G' THEN NETAMOUNT ELSE 0 END) - (CASE WHEN dbo.TBL_SAFE_MOVEMENT.GCKODU = 'C' THEN NETAMOUNT ELSE 0 END) / dbo.TBL_SAFE_MOVEMENT.PIECE AS YYYY
FROM
dbo.TBL_SAFE INNER JOIN
dbo.TBL_SAFE_MOVEMENT ON dbo.TBL_SAFE.CODE = dbo.TBL_SAFE_MOVEMENT.SAFECODE

Do something like shown below. I used table aliases to make it more readable (and it is a recommended best practice anyway).
SELECT
tsm.PIECE,
(CASE WHEN tsm.GCKODU = 'G' THEN NETAMOUNT ELSE 0 END) AS DEBIT,
(CASE WHEN tsm.GCKODU = 'C' THEN NETAMOUNT ELSE 0 END) AS CREDIT,
(CASE WHEN tsm.GCKODU = 'G' THEN NETAMOUNT ELSE 0 END) - (CASE WHEN tsm.GCKODU = 'C' THEN NETAMOUNT ELSE 0 END) AS XYZ,
tsm.DOWNPAYMENT,
v1.TotalSum,
v1.TotalSum/NULLIF(tsm.PIECE,0) AS DifferentColumn,
(CASE WHEN tsm.GCKODU = 'G' THEN NETAMOUNT ELSE 0 END) - (CASE WHEN tsm.GCKODU = 'C' THEN NETAMOUNT ELSE 0 END) / tsm.PIECE AS YYYY
FROM
dbo.TBL_SAFE AS ts INNER JOIN
dbo.TBL_SAFE_MOVEMENT AS tsm ON ts.CODE = tsm.SAFECODE
CROSS APPLY
( VALUES
(
(CASE WHEN tsm.GCKODU = 'G' THEN NETAMOUNT ELSE 0 END) - (CASE WHEN tsm.GCKODU = 'C' THEN NETAMOUNT ELSE 0 END) - tsm.DOWNPAYMENT )
)v1(TotalSum)

I think you probably want something like this:

 Select sm.PIECE
      , sm.DOWNPAYMENT
      , max(cd.Amount) As DebitCredit
      , sum(cd.Amount) As XYZ
      , sum(cd.Amount) - sum(sm.DOWNPAYMENT) As TotalSum
      , sum(cd.Amount) / sum(sm.PIECE) As YYYY
   From dbo.TBL_SAFE                     s
  Inner Join dbo.TBL_SAFE_MOVEMENT      sm On sm.SAFECODE = s.CODE
  Cross Apply (Values (Case When sm.GCKODU = 'G' Then s.NETAMOUNT Else s.NETAMOUNT * -1 End)) As cd(Amount);
  Group By
        sm.PIECE
      , sm.DOWNPAYMENT;

You cannot have the value GCKODU as both a 'G' and a 'C' on the same line - using the CROSS APPLY returns those values in a single column which you can then SUM. You could also wrap each case expression in a SUM but it is just easier to do perform the case expression once in the CROSS APPLY and then SUM the amount column in your other calculations.