The Old County The Old County - 3 months ago 8
MySQL Question

SQL Grouping by development type and summation of the value

I am working on an api to return a list of building projects.

SELECT
`sample`.`Development Type` AS 'Development Type',
`sample`.`Postcode` AS 'Postcode',
`sample`.`Value Desc` AS 'Value Desc'
FROM
`sample`
WHERE `sample`.`Postcode` LIKE '%SW1%'


and this returns a list of building projects.

Development Type | Postcode | Value Desc

Refurbishment & Extension | SW1E 6LB | 400,000
Refurbishment & Extension | SW1E 6LB | 400,000
Refurbishment & Extension | SW1X 8AL | 75,000
Refurbishment & Extension | SW1X 8AL | 75,000
Refurbishment & Extension | SW1P 3RE | 225,000
Refurbishment & Extension | SW1P 3RE | 225,000
Extension | SW1X 7QU | 200,000
Extension | SW1X 7QU | 200,000
Refurbishment | SW1X 8AL | 75,000


I need to modify the query to get an output like this.

Development Type | Sum Value Desc

Refurbishment & Extension | 700,000
Extension | 200,000
Refurbishment | 75,000


--so group by Development Type -- remove the duplicate result (could be a flag I should do a where query on, sum in the thousands to handle the comma)

Answer

This would be a simple GROUP BY query if not for that each record seems to appear in duplicate. But this can fixed by first using a subquery with SELECT DISTINCT to remove the duplicates, and then grouping from this subquery to get the sums you want:

SELECT t.`Development Type`, FORMAT(SUM(t.`Value Desc`), 0)
FROM
(
    SELECT DISTINCT `Development Type`, `Postcode`, `Value Desc`
    FROM sample
) t
GROUP BY t.`Development Type`
ORDER BY SUM(t.`Value Desc`) DESC

Follow the link below for a running demo:

SQLFiddle

Update:

Since your source data may be in CSV, you can change the outer SELECT statement to the following to handle the commas:

SELECT t.`Development Type`,
       FORMAT(SUM(CAST(REPLACE(`Value Desc`, ',' , '') AS UNSIGNED)), 0)

Also you can change the ORDER BY clause to this:

ORDER BY SUM(CAST(REPLACE(`Value Desc`, ',' , '') AS UNSIGNED)) DESC