Postgresql – Finding how many times a value is shown on a table – Postgresql

postgresql

I am trying to find employees that sold the most different type of subscriptions. The tables are:

  • Employee(PK employee_id, name, email, phone_number)
  • Person(PK email, …)
  • Order (PK invoice_id, customer_name, customer_email, FK product_id)
  • Product (PK product_id, FK subscription_type, service_provider)
  • Subscription(PK subscription_type, price)

I can get some results but instead of finding how many types of subscription an employee sold, I get the number of subscriptions sold by the employee by subscription type. 

SELECT COUNT (employee_id),employee_id,
                            s.subscription_type AS type
FROM employee
INNER JOIN person p USING(email)
INNER JOIN order o USING(email)
INNER JOIN product pr ON o.product_id = pr.product_id
INNER JOIN subscription s ON pr.subscription_type = s.subscription_type
GROUP BY employee_id,
         s.s
ORDER BY employee_id

an example:

    +-------+-------------------------------+----------+
    | count | employee_id                   | type     |
    +-------+-------------------------------+----------+
    |     3 |                          1111 | monthly  |
    |     2 |                          1111 | yearly   |
    |     2 |                          2222 | monthly  |
    |     1 |                          2222 | lifetime |
    |     5 |                          2222 | yearly   |
    +-------+-------------------------------+----------+

but I don't want to see how many monthly he sold, I want to see how many different types he sold. In this case that would be (111,2) and (222,3). I am missing something obvious here, please help me find it.

Best Answer

Test

SELECT employee.employee_id,
       COUNT(DISTINCT pr.subscription_type) AS types_count
FROM employee
INNER JOIN person p USING(email)
INNER JOIN order o USING(email)
INNER JOIN product pr ON o.product_id = pr.product_id
-- INNER JOIN subscription s ON pr.subscription_type = s.subscription_type
GROUP BY employee.employee_id
ORDER BY employee_id
Related Question