MySQL SQL查询求助：按groupe与日期转置数据展示单一数值

Hey there! Let's work through this problem you're having—getting your MySQL data grouped by groupe and date, then transposed so each pair shows a single value. I'll cover the most common scenarios and solutions below.

First, Let's Start with an Example Table #

Let’s assume your table looks something like this (adjust names to match your actual schema):

CREATE TABLE your_data (
  groupe VARCHAR(50),
  record_date DATE,
  int_value INT,
  -- Optional: if you have a category/metric type you want to transpose into columns
  metric_type VARCHAR(50)
);

Scenario 1: Collapse Multiple Values into One per Groupe-Date Pair #

If you have multiple rows for the same groupe and date, and you need to condense them into a single value (sum, average, max, etc.), this is straightforward with basic grouping and aggregation:

SELECT
  groupe,
  record_date,
  -- Pick the aggregation that fits your needs: SUM, AVG, MAX, MIN, etc.
  SUM(int_value) AS aggregated_value
FROM your_data
GROUP BY groupe, record_date
ORDER BY groupe, record_date;

This will output one row per groupe-date combination, with a single aggregated integer value as required.

Scenario 2: Transpose Categories into Separate Columns #

If you need to turn different categories (like metric_type in our example) into distinct columns for each groupe-date pair, use CASE statements paired with aggregation. This works for all MySQL versions:

SELECT
  groupe,
  record_date,
  -- Map each category to its own column
  MAX(CASE WHEN metric_type = 'sales' THEN int_value END) AS sales,
  MAX(CASE WHEN metric_type = 'visits' THEN int_value END) AS visits,
  MIN(CASE WHEN metric_type = 'conversions' THEN int_value END) AS conversions
FROM your_data
GROUP BY groupe, record_date
ORDER BY groupe, record_date;

If you have dynamic categories (you don't know all possible values upfront), use dynamic SQL to auto-generate the transposition logic:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN metric_type = ''',
      metric_type,
      ''' THEN int_value END) AS ',
      metric_type
    )
  ) INTO @sql
FROM your_data;

SET @sql = CONCAT('SELECT groupe, record_date, ', @sql, ' FROM your_data GROUP BY groupe, record_date');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Common Fixes for Failed Queries #

If your original query didn't work, double-check these:

• Did you include both groupe and date in your GROUP BY clause? MySQL requires all non-aggregated columns to be listed here.
• Are you using the right aggregation function? Using SUM when you need MAX (or vice versa) will skew your results.
• For transposition: Did you pair each CASE with an aggregation function (like MAX/MIN)? Without it, you'll get unexpected NULLs or duplicate rows.

内容的提问来源于stack exchange，提问作者Julien L.