I have two tables

First table - ticket history:

2nd table - companies and corresponding vehicles:

If a customer took both plane and car, then they are "mixed". Otherwise they are "plane" or "car" customers. How can I get the result below?

CodePudding user response：

Your title is misleading, you need to specify which part you are having problem.

May not be the best answer. Tested in MYSQL env, sql fiddle

    select transportation,
    sum(no_of_shuttle) as no_of_shuttle_took,
    round(avg(ticket_price), 2) as avg_price_per_customer,
    count(customer_id) as no_of_customer
    from (
          select 
          customer_id, 
          'mixed' as transportation, 
          count(transportation) as no_of_shuttle,
          sum(ticket_price) as ticket_price
          from tickets
          group by customer_id
          having count(distinct transportation) > 1

          union all

          select 
          customer_id, 
          transportation, 
          count(transportation) as no_of_shuttle,
          sum(ticket_price) as avg_ticket_price
          from tickets
          group by customer_id
          having count(distinct transportation) = 1
         ) t
    group by transportation

I am using subqueries to aggregate

1. customers with multiple distinct transportation type
2. customers with single distinct transportation type

Then I union these two results into one result set to further calculate the number of customers, number of shuttle took and average ticket price per customer. Note that I am rounding the price to 2 decimal places.

CodePudding user response：

SQL Server using a common table expression:

;WITH cte1 as (
    SELECT customer_id,CASE when count(distinct(transportation))>1 THEN 'Mixed' ELSE MAX(transportation) END as transportation, AVG(ticket_price) as avg_ticket_price,SUM(CASE WHEN vehicle='Shuttle' THEN 1 ELSE 0 END) as shuttle
    FROM history as a
    JOIN vehicle as b ON a.company_id=b.company_id 
    GROUP BY customer_id)
SELECT transportation,COUNT(DISTINCT(customer_id)) as num_cust, AVG(avg_ticket_price) as avg_ticket_price,sum(shuttle) as shuttle
FROM cte1
GROUP BY transportation