Brainfeeder Brainfeeder - 2 months ago 10
MySQL Question

SQL: Counting occurences and grouping main query on column

As a CRM user would love to know how many times he sold each item, I'm cracking my head for some time on this one.

Normaly I'ld get the total count and even the total income through some subqueries. The thing is this user has multiple 'unique' items with the same name, but different unit prices. He'ld like these grouped, and the amounts added to the printed row.

The SQL currently works, but if I add a

GROUP BY
it will, as expected only show unique names without making the total sum correctly.

SELECT
`app_separate_costs`.*,
(SELECT
SUM(`a`.`amount`) AS count
FROM (`app_invoice_rows` AS `a`)
WHERE `a`.`separate_cost_id` = `app_separate_costs`.`id`
) AS count,
(SELECT
SUM(`b`.`amount`)*(`b`.`unit_price`) AS total
FROM (`app_invoice_rows` AS `b`)
WHERE `b`.`separate_cost_id` = `app_separate_costs`.`id`
) AS total
FROM (`app_separate_costs`)
WHERE `app_separate_costs`.`is_text` = 0
AND `app_separate_costs`.`is_header` = 0
AND `app_separate_costs`.`name` != '-'
##GROUP BY `app_separate_costs`.`name` --> this will cause incorrect sum
ORDER BY `count` DESC


Could somebody help me with this one?

SAMPLE DATA

table app_invoice_rows

+------------------+--------------------------------------+--------+------------+
| separate_cost_id | name | amount | unit_price |
+------------------+--------------------------------------+--------+------------+
| 1 | Domein registratie | 4.00 | 16.00 |
| 1 | Domein registratie | 4.00 | 16.00 |
| 55 | Domein registratie | 2.00 | 20.00 |
| 1 | Domein registratie | 2.00 | 16.00 |
| 2 | BO - Randapperatuur | 2.00 | 123.14 |
| 6 | Additional IP Subnets | 3.00 | 8.06 |
| 99 | 2BY - Desktop | 1.00 | 809.00 |
| 1030 | Uit te voeren werken op verplaatsing | 2.00 | 65.00 |
| 1031 | Verplaatsing | 1.00 | 45.00 |
+------------------+--------------------------------------+--------+------------+


table app_separate_costs has id, name, unit_price as fields (amongst other irrelevant columns)

DESIRED RESULT

+-------------------------------------------+--------+------------+
| name | count | total |
+-------------------------------------------+--------+------------+
| Domein registratie | 13.00 | 200.00 |
| BO - Randapperatuur | 2.00 | 246.28 |
| Additional IP Subnets | 3.00 | 24.18 |
| 2BY - Desktop | 1.00 | 809.00 |
| Uit te voeren werken op verplaatsing | 2.00 | 130.00 |
| Verplaatsing | 1.00 | 45.00 |
+-------------------------------------------+--------+------------+

Answer Source
SELECT 
    `app_separate_costs`.*, 
    SUM(`a`.`amount`) AS count, 
    SUM((`a`.`amount`)*(`a`.`unit_price`)) AS total 
FROM (`app_separate_costs`) 
     INNER JOIN (`app_invoice_rows` AS `a`) ON `a`.`separate_cost_id` = `app_separate_costs`.`id`
WHERE `app_separate_costs`.`is_text` = 0 
    AND `app_separate_costs`.`is_header` = 0 
    AND `app_separate_costs`.`name` != '-' 
GROUP BY `app_separate_costs`.`name` 
ORDER BY `count` DESC

Guess it should be like this. JOIN the table once rather than using subqueries. After that group by name provides aggregated calculations for each name.