CODEWITHSUNDEEP
sql
bgrow11
bgrow11

Reputation: 31

SQL Query to Combine rows

I have a query that currently prints out what I need but doesn't combine the rows that I need. For example, I need the first two rows with the meter name to be combined together and so on for the rest of the meter names.

This is currently what I have:

SELECT      
    --Displays Client Number
    CASE
        WHEN v.MID IS NULL THEN ''
        ELSE v.ClientNumber
    END AS 'Client Number',

    --Displays MID Number
    CASE
        WHEN v.MID IS NULL THEN ''
        ELSE v.MID
    END AS MID,

    --Displays Meter Name
    CASE
        WHEN v.MID IS NULL THEN ''
        ELSE v.MeterName
    END AS 'Meter Name',
    /*
    CASE
        WHEN v.MID IS NULL THEN 0
        ELSE SUM(v.MCF)
    END AS MCF,
    */


    --Displays January's MCF
    CASE
        WHEN v.AccountPeriod = '2018-01-01' THEN SUM(v.MCF)
        ELSE 0
    END AS January,

    --Displays Febuary's MCF
    CASE
        WHEN v.AccountPeriod = '2018-02-01' THEN SUM(v.MCF)
        ELSE 0
    END AS Febuary,


    CASE
        WHEN v.MID IS NULL THEN 0
        ELSE SUM(v.mcf)
    END AS 'Meter Total'

FROM Volumes v
LEFT JOIN CurrentMeters cm 
    ON v.MID = cm.MID
WHERE cm.ClientNumber = 107500
GROUP BY v.MID, v.AccountPeriod, v.ClientNumber, v

Does anyone have any suggestions as to what I should do or how I should approach this?

Upvotes: 1

Views: 61

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

I think you just want conditional aggregation:

SELECT (CASE WHEN v.MID IS NULL THEN '' ELSE v.ClientNumber END) AS Client_Number,
       COALESCE(WHEN v.MID, '') as MID,
       (CASE WHEN v.MID IS NULL THEN '' ELSE v.MeterName END) AS Meter_Name,
      SUM((CASE WHEN v.MID IS NULL THEN 0 v.MCF END) AS MCF,
      SUM(CASE WHEN v.AccountPeriod = '2018-01-01' THEN v.MCF ELSE 0 END) AS January,
      SUM(CASE WHEN v.AccountPeriod = '2018-02-01' THEN v.MCF ELSE 0 END) AS January,
      SUM(CASE WHEN v.MID IS NULL THEN 0 ELSE v.mcf END) AS Meter_Total
FROM Volumes v JOIN
     CurrentMeters cm 
     ON v.MID = cm.MID
WHERE cm.ClientNumber = 107500
GROUP BY (CASE WHEN v.MID IS NULL THEN '' ELSE v.ClientNumber END),
         COALESCE(WHEN v.MID, ''),
        (CASE WHEN v.MID IS NULL THEN '' ELSE v.MeterName END);

Notes:

  • Your WHERE clause is turning the outer join into an inner join, so there is no reason to express an outer join.
  • The GROUP BY keys specify what defines a row in the result set. Each combination of unique values in the data is in exactly one row. Hence, you don't want AccountPeriod in the GROUP BY.
  • Presumably, you want one row for each of the expressions that define a row, so these expressions are in the group by.
  • For conditional aggregation, the CASE expression is an argument to an aggregation function such as SUM().

Upvotes: 1

Related Questions