0

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.

2
  • COUNT(DISTINCT type)?
    – Akina
    Dec 5, 2019 at 13:44
  • I am fairly new to SQL so sorry if I got you wrong. But if you meant to put COUNT(DISTINCT type) instead of COUNT(employee_id), I've tried that and it only turns every value in count to 1. @Akina
    – arty
    Dec 5, 2019 at 13:52

1 Answer 1

1

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 
2
  • Changed the name of the column that was faulty, but could you tell me why did you commented the last inner join?
    – arty
    Dec 5, 2019 at 14:10
  • @arthionne why did you commented the last inner join? None of its fields are used in the query (except ON clause). If the query gives wrong result - uncomment.
    – Akina
    Dec 5, 2019 at 16:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.