How do I group by on calculated columns?

group-by, sql

Solution

Simply `GROUP BY` your calculated columns:

SELECT 
    ROUND(Width/10,0) as W
    ,ROUND(Height/10,0) as H
    ,COUNT(*) as A -- You may replace this with SUM(Amount) too
FROM 
    IMAGES
GROUP BY 
    ROUND(Width/10,0)
    ,ROUND(Height/10,0)
ORDER BY 
    W
    ,H
    ,A

EDIT: Also, from what I understand of your question you want the `COUNT` not the `SUM` of the rows..., right?

Problem

Assume the following table with 3 numeric fields: ``` Images (table) -------------- Width Height Amount ``` Width and Height are image sizes in millimeters. Now I want to create a report about the amount of images grouped by their dimensions in centimeters. This means that I have to GROUP BY 2 non existing columns. I can do: ``` SELECT ROUND(Width/10,0) AS W , ROUND(Height/10,0) AS H , SUM(Amount) AS A FROM IMAGES GROUP BY Width , Height ORDER BY W , H , A ``` but this will do the mm to cm conversion only on the view level and will result in more than one row for same dimensions. e.g. ``` W H A --- --- - 150 159 2 150 160 1 ``` will not result in 1 category ``` W H A -- -- - 15 16 3 ``` but in ``` W H A -- -- - 15 16 2 15 16 1 ``` The targeted engine is actually a FileMaker database, that unfortunatly does not seem to support aggregate functions within the GROUP BY clause.

Original source