Combining two output values (columns) into one

Viewed 30

I am trying to combine SCO_NET_VALUE + FRONT_END_NET_VALUE into new a totals table.

Any assistance would be appreciated.

Sample output:

Sample

Query:

SELECT 
    storenumber,
    bbbtendertypetext,
    SUM(CASE WHEN TerminalNumber > 91 THEN (financialamounttendered - financialchangeamount) END) AS SCO_NET_VALUE,
    SUM(CASE WHEN TerminalNumber < 92 THEN (financialamounttendered - financialchangeamount) END) AS FRONT_END_NET_VALUE,
    COUNT(CASE WHEN TerminalNumber > 91 THEN (transactionid) END) AS Transaction_Count_SCO,
    COUNT(CASE WHEN TerminalNumber < 92 THEN (transactionid) END) AS Transaction_Count_Front_END
FROM   
    [AceTLogData].[Tlog].[tender]
WHERE  
    transactiondatetime > '2022-09-22 00:00:00.000'
    AND transactiondatetime < '2022-09-23 00:03:00.000'
GROUP BY 
    storenumber, bbbtendertypetext
ORDER BY 
    storenumber
1 Answers

Please, not that the columns 'SCO_NET_VALUE' and 'FRONT_END_NET_VALUE' may have a 'NULL' values due

case > 91 and case < 92

so, first of all you should decide what value be in 'SCO_NET_VALUE' column if value <= 91? is it 0? so, modify that portion to be

SUM(CASE WHEN TerminalNumber > 91 THEN (financialamounttendered - financialchangeamount) **else 0** END) AS SCO_NET_VALUE,
    SUM(CASE WHEN TerminalNumber < 92 THEN (financialamounttendered - financialchangeamount) **else 0** END) AS FRONT_END_NET_VALUE,

then you can do outer query to select sum of these two columns some thing like this:

select storenumber,
    bbbtendertypetext,
    SCO_NET_VALUE + FRONT_END_NET_VALUE as Total,
    Transaction_Count_SCO,
    Transaction_Count_Front_END
from
(

SELECT 
    storenumber,
    bbbtendertypetext,
    SUM(CASE WHEN TerminalNumber > 91 THEN (financialamounttendered - financialchangeamount) END) AS SCO_NET_VALUE,
    SUM(CASE WHEN TerminalNumber < 92 THEN (financialamounttendered - financialchangeamount) END) AS FRONT_END_NET_VALUE,
    COUNT(CASE WHEN TerminalNumber > 91 THEN (transactionid) END) AS Transaction_Count_SCO,
    COUNT(CASE WHEN TerminalNumber < 92 THEN (transactionid) END) AS Transaction_Count_Front_END
FROM   
    [AceTLogData].[Tlog].[tender]
WHERE  
    transactiondatetime > '2022-09-22 00:00:00.000'
    AND transactiondatetime < '2022-09-23 00:03:00.000'
GROUP BY 
    storenumber, bbbtendertypetext
) inner query

ORDER BY 
    storenumber
Related