-2

I have a database with 3 tables (customers, tickets, company_transactions).

customers(customer_id, full_name)

tickets(ticket_id, type, price, start_date, end_date, category)

company_transactions(transaction_id, customer_id, ticket_id, purchase_date, transaction)

Foreign Keys: customer_id -> customers.customer_id, ticket_id -> tickets.ticket_id

My goal is to get the highest number of purchases for each ticket category from table company_transactions. In other words I want to count each transaction made by every user for each category and then get the highest number of purchases for each category and who was the customer who made them.

select tickets.category, CT.customer_id, customers.full_name
    , count(category) as amount
    FROM company_transactions as CT
    INNER JOIN tickets ON CT.ticket_id = tickets.ticket_id
    INNER JOIN customers ON CT.customer_id = customers.customer_id
GROUP BY CT.customer_id, customers.full_name, tickets.category    
having count(category) =
            (select max(amount)
                 from (
                 select tickets.category, CT.customer_id, customers.full_name
                 , count(category) as amount
                 from company_transactions as CT
                 INNER JOIN tickets ON CT.ticket_id = tickets.ticket_id
                 INNER JOIN customers ON CT.customer_id = customers.customer_id
             group by CT.customer_id, customers.full_name, tickets.category
             ) as t
    )
order by amount DESC;

I was expecting to get the max number of ticket purchases for each category and who, was the customer (customer name) who has that number but instead only got the maximum number of ticket purchases for all categories together.

npateras
  • 9
  • 5

1 Answers1

0

Use distinct on:

SELECT DISTINCT ON (t.category) t.category, CT.customer_id, c.full_name, count(category) as amount
FROM company_transactions CT JOIN
     tickets t
     ON CT.ticket_id = t.ticket_id JOIN
     customers c
     ON CT.customer_id = c.customer_id
GROUP BY CT.customer_id, c.full_name, t.category    
ORDER BY t.category, amount DESC;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786