基于现有SQL修改：按指定USR统计检验零件数量总和

Alright, let's tweak your original SQL to calculate the total NbPiecesCTR grouped specifically by the "USR XXX 360" and "NO 720" categories. First, I fixed a few syntax issues in your original query (like trailing commas and missing subquery aliases) while adding the grouping logic:

SELECT 
    -- Create a grouped category for the two target types
    CASE 
        WHEN Utilisateur = 'USR XXX 360' THEN 'USR XXX 360'
        WHEN Utilisateur = 'NO 720' THEN 'NO 720'
        ELSE 'Other' -- Optional: Include if you want to catch non-matching records
    END AS InspectionCategory,
    NumOM, 
    Id_site, 
    SocieteClient, 
    Reference, 
    Designation, 
    SUM(NbPiecesCTR) AS TotalInspectedPieces
FROM (
    SELECT DISTINCT 
        OMpda.NumOM AS NumOM, 
        OMpda.Id_site AS Id_site, 
        OMpda.NumTerminal AS NumTerminal, 
        OMpda.SocieteClient AS SocieteClient, 
        OMpda.Utilisateur AS Utilisateur, 
        OMpda.AJ AS AJ, 
        OMpda.Datesaisie AS Datesaisie, 
        OMpda.Période AS Période, 
        OMpda.Reference AS Reference, 
        OMpda.Designation AS Designation, 
        OMpda.NumBac AS NumBac, 
        OMpda.Demarrage AS Demarrage, 
        OMpda.FinOM AS FinOM, 
        OMpda.NbPiecesCTR AS NbPiecesCTR
    FROM OMpda
) AS DistinctOMRecords -- Added alias for the subquery (required in most SQL dialects)
WHERE 
    Id_site = {pNomSite} 
    AND Datesaisie = {pDateOM} 
    AND NumOM = {pNumom}
    -- Optional: Uncomment if you only want results for these two categories
    -- AND Utilisateur IN ('USR XXX 360', 'NO 720')
GROUP BY 
    -- Match the CASE statement in SELECT to ensure proper grouping
    CASE 
        WHEN Utilisateur = 'USR XXX 360' THEN 'USR XXX 360'
        WHEN Utilisateur = 'NO 720' THEN 'NO 720'
        ELSE 'Other'
    END,
    Id_site, 
    NumOM, 
    SocieteClient, 
    Datesaisie, 
    Reference, 
    Designation

Key Changes Explained: #

• Fixed Syntax Issues: Removed trailing commas after the last field in both the subquery and main query, and added an alias for the subquery (most SQL databases require this).
• Added Grouping Logic: Used a CASE statement to categorize each record into "USR XXX 360", "NO 720", or "Other" (adjust if your category source isn't the Utilisateur field—just swap it for the correct column name).
• Aligned GROUP BY: Included the same CASE statement in the GROUP BY clause to ensure sums are calculated per category, along with your original grouping fields.
• Optional Filter: If you don't need to see "Other" records, uncomment the AND Utilisateur IN (...) line in the WHERE clause to only include the two target categories.

If the "USR XXX 360" and "NO 720" labels aren't tied to the Utilisateur field, just replace Utilisateur in the CASE statement with the correct column (like NumTerminal or a custom identifier column) and adjust the filter accordingly.

内容的提问来源于stack exchange，提问作者Koomsi