Sql - group by a range (m2) and sum the price of the elements in the range

Question

I'm sql rookie and I don't know how to proceed with this query.

I have this table m2AndPrices. It contains assets and their surface and value associated.

|---------------------------------------------|
|id     |     code      |   m2   |      value |
|---------------------------------------------|
|32980  | 143452asd10   | 11.17  |      0     |
|---------------------------------------------|
|25107  | 20152fgg219   | 57.00  |   55304,99 |
|---------------------------------------------|
|5692   | 203hhh75735   | 87.18  |   98334,16 |
|---------------------------------------------|
|31500  | 200788fgfg193 | 240.64 |  164401,75 |
|---------------------------------------------|

Grouping by square-meters(m2) and doing the count I get this output:

|----------------------------------|
|m2section |     number_of_assets  |
|----------------------------------|
|<1        |         175           |
|----------------------------------|
|1-5       |         286           |
|----------------------------------|
|5-10      |         374           |
|----------------------------------|
|10-20     |         573           |  
|----------------------------------|
|20-40     |        5212           |
|----------------------------------|
|40-80     |        3892           |
|----------------------------------|
|80-120    |        4121           |
|----------------------------------|
|120-180   |        1849           |
|----------------------------------|
|180-250   |         705           |
|----------------------------------|
|250-500   |         430           |
|----------------------------------|
| >500     |         131           |
|----------------------------------|

The code:

    SELECT * FROM (
    SELECT cast(m2section as varchar), COUNT(*) as number_of_assets FROM (
    SELECT 
    CASE 
        WHEN m2 <= 1 THEN '<1'
        WHEN m2 > 1 AND m2 <= 5 THEN '1-5'
        WHEN m2 > 5 AND m2 <= 10 THEN '5-10' 
        WHEN m2 > 10 AND m2 <= 20 THEN '10-20'
        WHEN m2 > 20 AND m2 <= 40 THEN '20-40'
        WHEN m2 > 40 AND m2 <= 80 THEN '40-80'
        WHEN m2 > 80 AND m2 <= 120 THEN '80-120'
        WHEN m2 > 120 AND m2 <= 180 THEN '120-180'
        WHEN m2 > 180 AND m2 <= 250 THEN '180-250'
        WHEN m2 > 250 AND m2 <= 500 THEN '250-500'
        WHEN m2 > 500 THEN '>500' END AS m2section  
FROM (select * from m2AndPrices x) as a
) as A 
GROUP BY m2section) as B
order by CASE
    WHEN m2section = '<1' THEN 1
    WHEN m2section = '1-5' THEN 2
    WHEN m2section = '5-10'  THEN 3 
    WHEN m2section = '10-20' THEN 4
    WHEN m2section = '20-40' THEN 5
    WHEN m2section = '40-80' THEN 6
    WHEN m2section = '80-120' THEN 7
    WHEN m2section = '120-180' THEN 8
    WHEN m2section = '180-250' THEN 9
    WHEN m2section = '250-500' THEN 10
    WHEN m2section = '>500' THEN 11
END
;

Sorry if It's not well indented.

I need to get another column that counts the value of the amount of the elements in each range like that:

|------------------------------------------------------|
|m2section |     number_of_assets  |  total value ($)
|------------------------------------------------------|
|<1        |         175           |    345.436
|------------------------------------------------------|
|1-5       |         286           |    864.364
|------------------------------------------------------|
|5-10      |         374           |   364.334
|------------------------------------------------------|
|10-20     |         573           |  1.364.364 
|------------------------------------------------------|
|20-40     |        5212           |  8.364.364
|------------------------------------------------------|
|40-80     |        3892           |  6.364.364
|------------------------------------------------------|
|80-120    |        4121           |     .....
|------------------------------------------------------|
|120-180   |        1849           |     .....
|------------------------------------------------------|
|180-250   |         705           |     .....
|------------------------------------------------------|
|250-500   |         430           |     .....
|------------------------------------------------------|
| >500     |         131           |     .....
|------------------------------------------------------|

But I don't know where can I make the count of the value ($).

Any idea will be apreciated.

Thanks a lot

score 1 · Accepted Answer · answered Jun 12 '18 at 10:53

In Postgres, I would recommend:

SELECT (CASE WHEN m2 <= 1 THEN '<1'
             WHEN m2 > 1 AND m2 <= 5 THEN '1-5'
             WHEN m2 > 5 AND m2 <= 10 THEN '5-10' 
             WHEN m2 > 10 AND m2 <= 20 THEN '10-20'
             WHEN m2 > 20 AND m2 <= 40 THEN '20-40'
             WHEN m2 > 40 AND m2 <= 80 THEN '40-80'
             WHEN m2 > 80 AND m2 <= 120 THEN '80-120'
             WHEN m2 > 120 AND m2 <= 180 THEN '120-180'
             WHEN m2 > 180 AND m2 <= 250 THEN '180-250'
             WHEN m2 > 250 AND m2 <= 500 THEN '250-500'
             WHEN m2 > 500 THEN '>500'
        END) AS m2section,
       COUNT(*) as num_assets,
       SUM(value) as sum_value 
FROM m2AndPrices mp
GROUP BY m2section
ORDER BY MIN(m2);

Now, I would also simplify the CASE expression:

SELECT (CASE WHEN m2 <= 1 THEN '<1'
             WHEN m2 <= 5 THEN '1-5'
             WHEN m2 <= 10 THEN '5-10' 
             WHEN m2 <= 20 THEN '10-20'
             WHEN m2 <= 40 THEN '20-40'
             WHEN m2 <= 80 THEN '40-80'
             WHEN m2 <= 120 THEN '80-120'
             WHEN m2 <= 180 THEN '120-180'
             WHEN m2 <= 250 THEN '180-250'
             WHEN m2 <= 500 THEN '250-500'
             WHEN m2 > 500 THEN '>500'
        END) AS m2section,
       COUNT(*) as num_assets,
       SUM(value) as sum_value 
FROM m2AndPrices mp
GROUP BY m2section
ORDER BY MIN(m2);

Notes:

CASE expressions are evaluated in order (guaranteed), so you don't need the AND expressions.
You can use column aliases in the GROUP BY. Column aliases are strongly recommended over column numbers, because the latter are deprecated in the most recent version of the SQL standard.
The ORDER BY is a bit of a trick, but it will order by the m2 values, which is what you really want.
I assume each row in the source table represents an asset, with no duplicates, so COUNT(*) is appropriate. If you can have duplicates, then you want COUNT(DISTINCT), but DISTINCT incurs overhead, so don't use it unless necessary.

Thanks, it's simplifies a lot the code. I take the advices – P.H Jun 12 '18 at 12:15 — P.H, Jun 12 '18 at 12:15
@P.H . . . I'm surprised you unaccepted the answer. – Gordon Linoff Jun 13 '18 at 11:15 — Gordon Linoff, Jun 13 '18 at 11:15

score 0 · Answer 2 · answered Jun 12 '18 at 10:21

I think you can likely simplify your query a lot here if you're working from just one originating table? M2ANDPrices

Firstly you want to categorise by m2, right?

SELECT 
  CASE
    WHEN m2 <= 1 THEN '<1'
    WHEN m2 > 1 AND m2 <= 5 THEN '1-5'
    WHEN m2 > 5 AND m2 <= 10 THEN '5-10' 
    WHEN m2 > 10 AND m2 <= 20 THEN '10-20'
    WHEN m2 > 20 AND m2 <= 40 THEN '20-40'
    WHEN m2 > 40 AND m2 <= 80 THEN '40-80'
    WHEN m2 > 80 AND m2 <= 120 THEN '80-120'
    WHEN m2 > 120 AND m2 <= 180 THEN '120-180'
    WHEN m2 > 180 AND m2 <= 250 THEN '180-250'
    WHEN m2 > 250 AND m2 <= 500 THEN '250-500'
    WHEN m2 > 500 THEN '>500' 
  END AS m2section
  FROM m2AndPrices
  GROUP BY 1 -- you can group by giving the index of the columns in the SELECT statement

This is the starting point. Now if you want to COUNT the number of rows, you use COUNT. For safety it's best practice to do a COUNT(DISTINCT id) so any duplicates are not double counted. In your case it would be COUNT(DISTINCT id) to count the number of sales or whatever is in your table

When you want to get the total value, you use SUM. So you'd do SUM(total_value)

Full code:

SELECT 
  CASE
    WHEN m2 <= 1 THEN '<1'
    WHEN m2 > 1 AND m2 <= 5 THEN '1-5'
    WHEN m2 > 5 AND m2 <= 10 THEN '5-10' 
    WHEN m2 > 10 AND m2 <= 20 THEN '10-20'
    WHEN m2 > 20 AND m2 <= 40 THEN '20-40'
    WHEN m2 > 40 AND m2 <= 80 THEN '40-80'
    WHEN m2 > 80 AND m2 <= 120 THEN '80-120'
    WHEN m2 > 120 AND m2 <= 180 THEN '120-180'
    WHEN m2 > 180 AND m2 <= 250 THEN '180-250'
    WHEN m2 > 250 AND m2 <= 500 THEN '250-500'
    WHEN m2 > 500 THEN '>500' 
  END AS m2section,
  COUNT(DISTINCT id) AS number_of_assets_per_m2_band,
  SUM(total_value) AS total_value_per_m2_band
  FROM m2AndPrices
  GROUP BY 1 -- you can group by giving the index of the columns in the SELECT statement 
  ORDER BY 1 DESC;

Using a COUNT(DISTINCT id) is especially helpful for when you start joining tables together where duplicates records can exist. For example joining customer contact cases with the emails associated with each case — SuperSecretAndNotSafeFromWork, Jun 12 '18 at 10:23

Sql - group by a range (m2) and sum the price of the elements in the range

2 Answers2