CODEWITHSUNDEEP
mysqlsqlgroup-bysubquery
Brainfeeder
Brainfeeder

Reputation: 2632

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      |
+-------------------------------------------+--------+------------+

Upvotes: 0

Views: 40

Answers (1)

StanislavL
StanislavL

Reputation: 57381

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.

Upvotes: 2

Related Questions