How to count in mysql-CodePudding

I have this query in mySQL where I would like to sum the line product of each doctor but I dont know how to do it.

use avant_medical;

select 
sales.doctor_id as DoctorID,
line_products.id as LineProductID,
line_products.name as LineProductName


from `doctors` 
inner join `sales` on `doctors`.`id` = `sales`.`doctor_id` 
inner join `inventories` on `sales`.`id` = `inventories`.`sale_id`
inner join `products` on `inventories`.`product_id` = `products`.`id`
inner join `line_products` on `products`.`lineProduct_id` = `line_products`.`id`


order by `doctors`.`id` asc;

 
lPID= lineProductID
|DrID|lPID |
| -- | ----|
| 1  | 7   |
| 1  | 6   |
| 1  | 6   |
| 1  | 7   |
| 1  | 7   |
| 1  | 7   |
| 1  | 6   |

This is how I want:

Doctor 1
lineID | quantity
  7    |    4
  6    |    3

I try this query only in mySQL

CodePudding user response：

The keyword you are looking for is count, not sum. Summing would add up every lineProductID as if they where regular mathematical values, while counting will add up how many times a given lineProductID is found.

select 
    sales.doctor_id as DoctorID,
    line_products.id as LineProductID,
    line_products.name as LineProductName,

-- We count the number of occurrences of each line_product.id
    COUNT(line_products.id) as LineProductQty

from `doctors` 
    inner join `sales` on `doctors`.`id` = `sales`.`doctor_id` 
    inner join `inventories` on `sales`.`id` = `inventories`.`sale_id`
    inner join `products` on `inventories`.`product_id` = `products`.`id`
    inner join `line_products` on `products`.`lineProduct_id` = `line_products`.`id`

-- Never forget to properly GROUP your aggregate functions, such as COUNT() or SUM()!
GROUP BY sales.doctor_id, line_products.id, line_products.name

order by `doctors`.`id` asc;

Since you didn't provided full schema to test this, I made a small, very artificial demo, but should be representative of how the query above works.