select 
  sum(case when Apt.productionValue != '-1.0' then Apt.subTotal 
    else Apt.productionValue end) as ProductionValue,Apt.date
from Appointment Apt
group by Apt.date 
order by Apt.date asc

I'm trying to get the sum of Production value only if it is over 0, else add subtotal instead for that row.

End goal is to still have 20 as the sum for 2021-09-02 and 45 for 2021-09-01

CodePudding user response：

You were close in your attempt. This should work based on the sample data provided.

select Apt.[Date]
    , sum(case when Apt.ProductionValue < 0 then Apt.SubTotal else Apt.ProductionValue end)
from Appointment  Apt
group by Apt.[Date]
order by Apt.[Date]

CodePudding user response：

You can select the expected column in a subquery like :

select Date, SUM(ProductionValue) as ProductionValue
from (
    select
        Date,
        case when ProductionValue > 0
            then ProductionValue 
            else SubTotal end
        as ProductionValue
    from Appointment
) as d
group by Date
order by Date

CodePudding user response：

It's not clear what you want to happen if there's a positive value in both columns, so one of these solutions should work depending on your scenario:

-- if you only care about SubTotal when ProductionValue < 0:
SELECT Date, ProductionValue = SUM
  ( CASE WHEN ProductionValue < 0 THEN SubTotal ELSE ProductionValue END)
FROM dbo.Appointment
GROUP BY Date
ORDER BY Date;

-- if you want positive values from both columns
SELECT Date, ProductionValue = SUM
  ( CASE WHEN ProductionValue < 0 THEN 0 ELSE ProductionValue END
    CASE WHEN SubTotal < 0 THEN 0 ELSE SubTotal END)
FROM dbo.Appointment
GROUP BY Date
ORDER BY Date;

• Example db<>fiddle