How to Handle Multiple Result Rows When Grouping and Ordering Data in SQL?

Handling multiple result rows in SQL grouping and sorting

In queries that group and sort data, you often encounter this situation: multiple rows with the same grouping key, but different values ​​for other columns. This may prevent obtaining the required aggregate data.

For example, a query that groups data by hardware model may return multiple rows with different "results" for the same model. To condense this into one row per model, you can modify the query so that actual 0 values ​​result in 0 values, and non-zero values ​​result in 1 values. However, this approach still produces multiple rows for models with multiple "result" values.

The key to achieving the desired aggregation is to group by the CASE expression rather than by the source column representing the result. By grouping by condition value, a query can combine rows with the same grouping key and condition result.

For example, the following query groups by model name, try type, and CASE expression used to convert the result value:

SELECT CURRENT_DATE-1 AS day, model.name, attempt.type, 
       CASE WHEN attempt.result = 0 THEN 0 ELSE 1 END, 
       count(*) 
FROM attempt attempt, prod_hw_id prod_hw_id, model model
WHERE time >= '2013-11-06 00:00:00'  
AND time < '2013-11-07 00:00:00'
GROUP BY model.name, attempt.type, CASE WHEN attempt.result = 0 THEN 0 ELSE 1 END
ORDER BY model.name, attempt.type, CASE WHEN attempt.result = 0 THEN 0 ELSE 1 END;

Alternatively, you can use the column alias of the CASE expression to distinguish it from the result source column:

SELECT CURRENT_DATE-1 AS day, model.name, attempt.type, 
       CASE WHEN attempt.result = 0 THEN 0 ELSE 1 END AS result1, 
       count(*) 
FROM attempt attempt, prod_hw_id prod_hw_id, model model
WHERE time >= '2013-11-06 00:00:00'  
AND time < '2013-11-07 00:00:00'
GROUP BY model.name, attempt.type, result1
ORDER BY model.name, attempt.type, result1;

It is important to remember that grouping by CASE expression using positional references (e.g., "GROUP BY 1,2,3") is more resistant to changes in the SELECT list than using column names in the GROUP BY clause.

The above is the detailed content of How to Handle Multiple Result Rows When Grouping and Ordering Data in SQL?. For more information, please follow other related articles on the PHP Chinese website!