CODEWITHSUNDEEP
sqlrollup
00robinette
00robinette

Reputation: 567

Sum rows using ROLLUP

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.

Upvotes: 0

Views: 123

Answers (2)

00robinette
00robinette

Reputation: 567

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

Upvotes: 0

minatverma
minatverma

Reputation: 1099

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 .

Upvotes: 0

Related Questions