How do I group by on calculated columns?

Question

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.

Answer 1

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?