Distinguishing which value is obtained from COALESCE by concatenating the value to a different prefi-CodePudding

SELECT COALESCE(grouped_wells.groupforecasting_id,wells.id) as the_id,  string_agg(wells.name,', ') as well_name, sum(gas_cd) as gas_cd, date 
FROM productions
INNER JOIN completions on completions.id = productions.completion_id 
INNER JOIN wellbores on wellbores.id = completions.wellbore_id 
INNER JOIN wells on wells.id = wellbores.well_id 
INNER JOIN fields on fields.id = wells.field_id 
INNER JOIN clusters on clusters.id = fields.cluster_id
     
     
LEFT JOIN grouped_wells on grouped_wells.wells_id = wells.id
LEFT JOIN groupforecasting on groupforecasting.id = grouped_wells.groupforecasting_id and groupforecasting.workspace_id = 3
     
     
GROUP BY the_id, productions.date
ORDER BY the_id, productions.date

In the above SQL, I am grouping by the_id that results from COALESCE. I want to avoid grouping grouped_wells.groupforecasting_id of equal value to wells.id.
One possible way is to add a prefix depending on which of the two value I got from COALESCE, either add "group_" or "well_".

How to add this conditional prefix to the_id?

If grouped_wells.groupforecasting_id is Null then concatenate "well_" to the result of COALESCE.
Else concatenate "group_" to the result of COALESCE.

CodePudding user response：

You nearly had the solution in your

If grouped_wells.groupforecasting_id is Null then concatenate "well_" to the result of COALESCE. Else concatenate "group_" to the result of COALESCE.

In postgres you can use the case when syntax:

case when ... then ...
else ... 
end as the_id

completing it in your select (and your comment):

    SELECT 
    case when grouped_wells.groupforecasting_id is NULL then concat('well_', wells.id)
         else 
    concat('group_', grouped_wells.groupforecasting_id) 
    end as the_id,
    string_agg(wells.name,', ') as well_name,
    sum(gas_cd) as gas_cd, date 
        FROM productions
        INNER JOIN completions on completions.id = productions.completion_id 
        INNER JOIN wellbores on wellbores.id = completions.wellbore_id 
        INNER JOIN wells on wells.id = wellbores.well_id 
        INNER JOIN fields on fields.id = wells.field_id 
        INNER JOIN clusters on clusters.id = fields.cluster_id
             
             
        LEFT JOIN grouped_wells on grouped_wells.wells_id = wells.id
        LEFT JOIN groupforecasting on groupforecasting.id = grouped_wells.groupforecasting_id and groupforecasting.workspace_id = 3
             
             
        GROUP BY the_id, productions.date
        ORDER BY the_id, productions.date