Combining multiple results into result

Artie 0
Viewed 33

I'm trying to get an obsolete number value for two of my columns but because my case statement contains terminalid column I am required to group by terminal id which gives me multiple values.

Query:

SELECT
    storenumber,
    bbbtendertypetext,
    
    CASE WHEN terminalnumber > 91 THEN
        SUM( financialamounttendered ) - SUM( financialchangeamount )
    END AS sco_net_value,

    CASE WHEN terminalnumber < 91 THEN
        SUM( financialamounttendered ) - SUM( financialchangeamount )
    END AS front_end_net_value,

    COUNT( transactionid ) AS transaction_count

FROM
    AceTLogData.Tlog.tender

WHERE
    transactiondatetime > '2022-09-21 00:00:00.000'
    AND
    transactiondatetime < '2022-09-22 00:03:00.000'

GROUP BY
    storenumber,
    terminalnumber,
    bbbtendertypetext

ORDER BY
    storenumber;

**Sample output **:

enter image description here

In the image you can see visa and gift card is coming in twice since they belong to two different terminals. So I am trying to see on how can I combine those duplicates in one so my SCO net sales, and transaction count would update as well.

sql-server sql
2 Answers

You can use CASE expression inside aggregate function call, then you don't need to include the column in GROUP BY section. Something like that:

WITH d AS (
    SELECT 6 AS storeNumber, 'GiftCard' AS tenderType, 21 as terminalNumber, 243 AS transactionId, 42 AS amount
    UNION ALL SELECT 6 AS storeNumber, 'GiftCard' AS tenderType, 100 as terminalNumber, 243 AS transactionId, 27 AS amount
)
SELECT
    storeNumber,
    tenderType,
    SUM(CASE WHEN terminalNumber < 91 THEN amount ELSE 0 END) AS sumTerminalType1,
    SUM(CASE WHEN terminalNumber > 91 THEN amount ELSE 0 END) AS sumTerminalType2
FROM d
GROUP BY storeNumber, tenderType
n0rd0

SELECT storenumber, bbbtendertypetext, Sum(case when TerminalNumber>91 then (financialamounttendered - financialchangeamount)end) AS SCO_NET_VALUE, Sum(case when TerminalNumber<91 then (financialamounttendered - financialchangeamount)end) AS FRONT_END_NET_VALUE, Count(transactionid) AS Transaction_Count FROM [AceTLogData].[Tlog].[tender] WHERE transactiondatetime > '2022-09-21 00:00:00.000' AND transactiondatetime < '2022-09-22 00:03:00.000' GROUP BY storenumber, bbbtendertypetext`` ORDER BY storenumber;

Artie0
Related