SQL: Group By with Case Statement for multiple fie

问题:

I am trying to write a GROUP BY clause with a CASE statement so I can conditionally GROUP BY according to the value of the parameter in my query. Here is my query that I am using (significantly shortened) and I keep getting "RVPname is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. And DVPname is invalid and AEname is invalid.

SELECT
DVPname, RVPname, AEname, Dealer, Product, Distribution, 

CASE WHEN @LEVEL = 'DVP' THEN DVPname
WHEN @LEVEL = 'RVP' THEN RVPname
WHEN @LEVEL = 'AE' THEN AEname
END AS NAME

..........other code here.....

GROUP BY Product, Distribution,
CASE WHEN @LEVEL = 'RVP' THEN AEname WHEN @LEVEL = 'DVP' THEN RVPname WHEN @LEVEL= 'AE' THEN Dealer END

--Does anyone know how to do what I am trying to accomplish. When LEVEL='DVP' I want to GROUP BY RVPname, when LEVEL='RVP' I want to GROUP BY AEname....make sense?

回答1:

A group by query without aggregates (such as your query) will only be useful for removing duplicates. So your options are to add aggregates for columns in the select clause that aren't in the group by or use the query to remove duplicates.

The following code removes all of the columns (DVPname, RVPname, AEname, Dealer) that are only conditionally in the group by statement (it will hit an error whenever they are not in the group by but are in the select). It also adds the name to the group by so that it can be in the select clause.

SELECT
Product, Distribution,
CASE 
    WHEN @LEVEL = 'DVP' THEN DVPname
    WHEN @LEVEL = 'RVP' THEN RVPname
    WHEN @LEVEL = 'AE' THEN AEname
END AS NAME,
CASE 
     WHEN @LEVEL = 'RVP' THEN AEname 
     WHEN @LEVEL = 'DVP' THEN DVPname 
     WHEN @LEVEL= 'AE' THEN Dealer 
END 

..........other code here.....

GROUP BY Product, Distribution, 
CASE 
    WHEN @LEVEL = 'DVP' THEN DVPname
    WHEN @LEVEL = 'RVP' THEN RVPname
    WHEN @LEVEL = 'AE' THEN AEname
END,
CASE 
     WHEN @LEVEL = 'RVP' THEN AEname 
     WHEN @LEVEL = 'DVP' THEN DVPname 
     WHEN @LEVEL= 'AE' THEN Dealer 
END 

回答2:

You will need to select all of your columns that are not in the GROUP BY clause with an aggregate function such as AVG() or SUM(). Otherwise, the database doesn't know what to do with the multiple entries that are returned with grouped records.

For example, your select statement should start out with something like this:

SELECT
SUM(DVPname), AVG(RVPname),

If they are text entries and you know they are all the same, you can also add they to the GROUP BY clause.

GROUP BY DVPname, RVPname, AEname, Dealer, Product, Distribution

In addition, here is a good format for your group by case statement:

GROUP BY
CASE WHEN @SortColumn = '0'
THEN [id] END,
CASE WHEN @SortColumn = '1'
THEN [name] END;

回答3:

If you just want conditional group by then I would suggest using a subquery:

select name, . ..
from (select (CASE WHEN @LEVEL = 'DVP' THEN DVPname
                   WHEN @LEVEL = 'RVP' THEN RVPname
                   WHEN @LEVEL = 'AE' THEN AEname
              END
             ) AS NAME, t.*
      from t
     ) t
group by name;

However, you lose all the other fields that you want. In most databases, you can't include columns in the select unless they are aggregated or in the group by clause. Here is an alternative for getting an arbitrary value from the columns:

SELECT min(DVPname), min(RVPname), min(AEname), min(Dealer), min(Product),
       min(Distribution),
       . . .
from (select (CASE WHEN @LEVEL = 'DVP' THEN DVPname
                   WHEN @LEVEL = 'RVP' THEN RVPname
                   WHEN @LEVEL = 'AE' THEN AEname
              END
             ) AS NAME, t.*
      from t
     ) t
group by name;

This returns the min values for the columns.

回答4:

I know of two methods: GROUP BY (Case statement 1, Case statement 2) and subquery. I determine how many rows each method will take and then base my process on that.

ex. (group by):

    SELECT
        CASE
            WHEN (situation A)
            THEN (result A)
            WHEN (situation B)
            THEN (result B)
        END column1,
        CASE
            WHEN (situation C)
            THEN (result C)
            WHEN (situation D)
            THEN (result D)
        END column2,
        SUM(AMOUNT) as TotalAMOUNT
    FROM aTable
    GROUP BY 
        CASE
            WHEN (situation A)
            THEN (result A)
            WHEN (situation B)
            THEN (result B)
        END,
        CASE
            WHEN (situation C)
            THEN (result C)
            WHEN (situation D)
            THEN (result D)
        END

ex. (Subquery)

    SELECT
        Column1,
        Column2,
        SUM(AMOUNT) as TotalAMOUNT
    FROM
        (
        SELECT
            CASE
                WHEN (situation A)
                THEN (result A)
                WHEN (situation B)
                THEN (result B)
            END column1,
            CASE
                WHEN (situation C)
                THEN (result C)
                WHEN (situation D)
                THEN (result D)
            END column2,
            AMOUNT
        FROM aTable
        )CaseTable
    GROUP BY Column1, Column2
    ORDER BY Column1 asc, SUM(AMOUNT) 

I think important to remember is that you can't contain additional details beyond what you're grouping together. In the above two examples, the results of your query are grouped by the variable you specify. You could also tack on some type of status depending on your variable, and then roll it up by the additional column, and choose not to display the final column in the rollup.