Sum rows using ROLLUP

Question

I have written a query that produces the following results.

The query looks like

SELECT
    rlrDivision.strLangue1 AS Division,
    COUNT(p.pkPoste) AS NumbersToAdd,
    SUM(p.iNbPostes) AS MoreNumbersToAdd 
FROM 
    poste p
LEFT JOIN 
    lien_objet_ref_8 lienDiv ON lienDiv.fkObjet = p.pkPoste
LEFT JOIN 
    r_referentiel rrDivision ON rrDivision.pkReferentiel =        lienDiv.fkReferentiel
LEFT JOIN 
    r_libelle_ref rlrDivision ON rlrDivision.pkLibelleRef = rrDivision.fkLib1
LEFT JOIN 
    lien_objet_liste_3 lol3 ON lol3.fkObjet = p.pkPoste AND   lol3.`strContexte`='descriptif/type_contrat'
LEFT JOIN 
    r_liste_editable rle ON lol3.fkListe = rle.pkListeEditable
LEFT JOIN 
    r_libelle_ref rlr ON rle.fkLibelleRef = rlr.pkLibelleRef
WHERE 
    p.pkPoste <> 1
    AND p.iSuppr <> 1
    AND p.iDernierEtatPoste = 2
    AND rlr.strLangue1 LIKE 'Full-time%'
GROUP BY 
    rlrDivision.strLangue1, p.`iNbPostes` WITH ROLLUP

This almost produces the correct results. However, I would like to have only one row for each Division, which without the ROLLUP I have. However, I do not have the desired totals. So basically I want what is below but with another row for totals on NumbersToAdd and MoreNumbersToAdd.

What is your RDBMS? Does it support [`ROLLUP`](https://technet.microsoft.com/en-us/library/ms189305(v=sql.90).aspx)? — PM 77-1, Feb 05 '16 at 03:15
@GordonLinoff Sorry, I changed my alias names so that it makes more sense now. — 00robinette, Feb 05 '16 at 03:22
I think you should store that in a temporal table or variable table and then perform two queries, one being a simple `SELECT` and the other doing the `SUM()` of those fields. — Andrew, Feb 05 '16 at 03:23

score 0 · Answer 1 · answered Feb 05 '16 at 03:37

you can do UNION :

SELECT
rlrDivision.strLangue1 AS Division,
COUNT(p.pkPoste) AS NumbersToAdd,
SUM(p.iNbPostes) AS MoreNumbersToAdd 
FROM poste p
LEFT JOIN lien_objet_ref_8 lienDiv ON lienDiv.fkObjet = p.pkPoste
LEFT JOIN r_referentiel rrDivision ON rrDivision.pkReferentiel =        lienDiv.fkReferentiel
LEFT JOIN r_libelle_ref rlrDivision ON rlrDivision.pkLibelleRef = rrDivision.fkLib1

LEFT JOIN lien_objet_liste_3 lol3 ON lol3.fkObjet = p.pkPoste AND   lol3.`strContexte`='descriptif/type_contrat'
LEFT JOIN r_liste_editable rle ON lol3.fkListe = rle.pkListeEditable
LEFT JOIN r_libelle_ref rlr ON rle.fkLibelleRef = rlr.pkLibelleRef
WHERE p.pkPoste <> 1
AND p.iSuppr <> 1
AND p.iDernierEtatPoste = 2
AND rlr.strLangue1 LIKE 'Full-time%'
GROUP BY rlrDivision.strLangue1

UNION ALL

SELECT 'Total' as Division
    , COUNT(p.pkPoste) as NumbersToAdd
    , SUM(p.iNbPostes) AS MoreNumbersToAdd 
FROM poste p
LEFT JOIN lien_objet_ref_8 lienDiv ON lienDiv.fkObjet = p.pkPoste
LEFT JOIN r_referentiel rrDivision ON rrDivision.pkReferentiel =        lienDiv.fkReferentiel
LEFT JOIN r_libelle_ref rlrDivision ON rlrDivision.pkLibelleRef = rrDivision.fkLib1
LEFT JOIN lien_objet_liste_3 lol3 ON lol3.fkObjet = p.pkPoste AND   lol3.`strContexte`='descriptif/type_contrat'
LEFT JOIN r_liste_editable rle ON lol3.fkListe = rle.pkListeEditable
LEFT JOIN r_libelle_ref rlr ON rle.fkLibelleRef = rlr.pkLibelleRef
WHERE p.pkPoste <> 1
AND p.iSuppr <> 1
AND p.iDernierEtatPoste = 2
AND rlr.strLangue1 LIKE 'Full-time%'
GROUP BY rlrDivision.strLangue1
;

There are other ROLL UP functions available for doing total in query , but not sure if you database would support that , as you haven't mentioned your database name .

@minatvermaj I am not sure this is acceptable in sqlPro I am getting the error Unknown column 'MoreNumbersToAdd' in 'field list' — 00robinette, Feb 05 '16 at 03:46
Actually it seems ROLLUP is supported but again not on an alias — 00robinette, Feb 05 '16 at 03:58
@00robinette I have updated the answer . Can you please check . — minatverma, Feb 05 '16 at 05:02
that isn't it either. I will play with it. That query produces 3 extra rows named title with the same numbers as the divisions - so 6, 12, 37 and 9, 12, and 257. — 00robinette, Feb 05 '16 at 12:52

score 0 · Answer 2 · answered Feb 05 '16 at 14:38

ROLLUP was the correct approach, but since a lot of work was put into figuring out how to properly execute I am answering my own question. Thanks for the input.

    SELECT 
    rlrDivision.strLangue1 AS Division,
    COUNT(p.pkPoste) AS NumbersToAdd,
    SUM(p.iNbPostes) AS MoreNumbersToAdd 
    FROM poste p
    LEFT JOIN lien_objet_ref_8 lienDiv ON lienDiv.fkObjet = p.pkPoste
    LEFT JOIN r_referentiel rrDivision ON rrDivision.pkReferentiel =                 lienDiv.fkReferentiel
    LEFT JOIN r_libelle_ref rlrDivision ON rlrDivision.pkLibelleRef = rrDivision.fkLib1
    LEFT JOIN lien_objet_liste_3 lol3 ON lol3.fkObjet = p.pkPoste AND   lol3.`strContexte`='descriptif/type_contrat'
    LEFT JOIN r_liste_editable rle ON lol3.fkListe = rle.pkListeEditable
    LEFT JOIN r_libelle_ref rlr ON rle.fkLibelleRef = rlr.pkLibelleRef
    WHERE p.pkPoste <> 1
    AND p.iSuppr <> 1
AND p.iDernierEtatPoste = 2
AND rlr.strLangue1 LIKE 'Full-time%'
GROUP BY rlrDivision.strLangue1 WITH ROLLUP

Sum rows using ROLLUP

2 Answers2