无需WITH ROLLUP实现MySQL多列汇总及phpgrid适配问题

Hey there, let's break down your problem and fix this step by step. First, let's address why your original approaches failed:

• The subquery error (Unknown column 'i.signedupdate' in 'where clause') happens because phpGrid likely appends filter conditions referencing the original table alias i to the outer query—but the outer query only knows the subquery alias t, not i.
• The conflict between WITH ROLLUP and ORDER BY is a MySQL restriction: you can't use both clauses directly together since ROLLUP modifies grouping order.

Instead of relying on WITH ROLLUP, we can use UNION ALL to manually add a grand total row to your grouped results. This avoids both issues and keeps your phpGrid filtering functional.

Step 1: Fix the Original Query (Remove Invalid Column) #

First, note that your original query includes i.id in the SELECT clause while grouping by c.Adviser—this is invalid in strict SQL modes (and returns arbitrary data even if allowed). We'll remove this column since it serves no purpose in grouped results.

Step 2: Use UNION ALL for Grand Total #

Here's the revised query that combines grouped adviser data with a grand total row:

SELECT 
    Adviser,
    Jan, Feb, Mar, Apr, May, Jun,
    Jul, Aug, Sept, Oct, Nov, Dece,
    Total
FROM (
    -- Grouped data per adviser
    SELECT 
        c.Adviser AS Adviser, 
        SUM(Month(i.SignedUpDate) = 1) AS Jan, 
        SUM(Month(i.SignedUpDate) = 2) AS Feb, 
        SUM(Month(i.SignedUpDate) = 3) AS Mar, 
        SUM(Month(i.SignedUpDate) = 4) AS Apr, 
        SUM(Month(i.SignedUpDate) = 5) AS May, 
        SUM(Month(i.SignedUpDate) = 6) AS Jun, 
        SUM(Month(i.SignedUpDate) = 7) AS Jul, 
        SUM(Month(i.SignedUpDate) = 8) AS Aug, 
        SUM(Month(i.SignedUpDate) = 9) AS Sept, 
        SUM(Month(i.SignedUpDate) = 10) AS Oct, 
        SUM(Month(i.SignedUpDate) = 11) AS Nov, 
        SUM(Month(i.SignedUpDate) = 12) AS Dece, 
        COUNT(i.id) AS Total,
        0 AS sort_order -- Ensures grouped rows come first
    FROM tbl_lead i 
    INNER JOIN tbl_clients c ON i.client_id = c.client_id 
    -- Add any static filters here (e.g., year = 2024)
    GROUP BY c.Adviser

    UNION ALL

    -- Grand total row
    SELECT 
        'GRAND TOTAL' AS Adviser, 
        SUM(Month(i.SignedUpDate) = 1) AS Jan, 
        SUM(Month(i.SignedUpDate) = 2) AS Feb, 
        SUM(Month(i.SignedUpDate) = 3) AS Mar, 
        SUM(Month(i.SignedUpDate) = 4) AS Apr, 
        SUM(Month(i.SignedUpDate) = 5) AS May, 
        SUM(Month(i.SignedUpDate) = 6) AS Jun, 
        SUM(Month(i.SignedUpDate) = 7) AS Jul, 
        SUM(Month(i.SignedUpDate) = 8) AS Aug, 
        SUM(Month(i.SignedUpDate) = 9) AS Sept, 
        SUM(Month(i.SignedUpDate) = 10) AS Oct, 
        SUM(Month(i.SignedUpDate) = 11) AS Nov, 
        SUM(Month(i.SignedUpDate) = 12) AS Dece, 
        COUNT(i.id) AS Total,
        1 AS sort_order -- Puts grand total at the end
    FROM tbl_lead i 
    INNER JOIN tbl_clients c ON i.client_id = c.client_id 
    -- Match filters from the grouped query above
) AS combined_results
-- Apply phpGrid dynamic filters here (e.g., WHERE Adviser = ?)
ORDER BY sort_order, Adviser;

Key Details: #

1. sort_order Column: This ensures the grand total row always appears at the bottom, regardless of how you sort the adviser names.
2. Filter Consistency: If you use phpGrid's dropdown filters, make sure to apply the same filter conditions to both parts of the UNION ALL (or add them to the outer WHERE clause for simplicity).
3. Valid Grouping: By removing i.id from the select, we comply with SQL standards and avoid unexpected behavior.

phpGrid Integration #

When configuring this query in phpGrid, use the entire SQL string as your data source. Any dynamic filters added by phpGrid will target the combined_results alias, which includes all columns needed for filtering and display.

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