Grouping rows from select statement-CodePudding

I have a SQL query which is giving me the output like:

Query:

SELECT LineBaseOP.LineName, COUNT(CekimOzelligiBasedOP.RecID) * 3 AS CekimSayisi3Var
FROM     LineBaseOP INNER JOIN
                  CekimOzelligiBasedOP ON LineBaseOP.LineName = CekimOzelligiBasedOP.LineName AND LineBaseOP.OperationNo = CekimOzelligiBasedOP.OperationNo AND 
                  LineBaseOP.MachineName = CekimOzelligiBasedOP.MachineNumber
WHERE  (LineBaseOP.FactoryId = 1)
GROUP BY LineBaseOP.LineName

Output:

LineName|CekimSayisi3Var|
-------- --------------- 
L11     | 255           |
L2      | 255           | 
L3      | 43            |
L4      | 143           |
L5      | 121           |
L6      | 101           |

I'm trying to group that output like that:

L2,L3,L4,L5 as FH

L11, L6 as SH

so my desired output needs to look like:

GroupName|CekimSayisi3VarGrouped|
--------- ---------------------- 
FH       | 562                  |
SH       | 356                  |

Any suggestions to how can I achieve that?

CodePudding user response：

You may aggregate using a CASE expression:

SELECT
    CASE WHEN lbop.LineName IN ('L2', 'L3', 'L4', 'L5')
         THEN 'FH'
         WHEN lbop.LineName IN ('L6', 'L11')
         THEN 'SH' END AS LineName,
    COUNT(cobop.RecID) * 3 AS CekimSayisi3Var
FROM LineBaseOP lbop
INNER JOIN CekimOzelligiBasedOP cobop
    ON lbop.LineName = cobop.LineName AND
       lbop.OperationNo = cobop.OperationNo AND 
       lbop.MachineName = cobop.MachineNumber
WHERE
    lbop.FactoryId = 1
GROUP BY
    CASE WHEN lbop.LineName IN ('L2', 'L3', 'L4', 'L5')
         THEN 'FH'
         WHEN lbop.LineName IN ('L6', 'L11')
         THEN 'SH' END;