I know there are a lot of topics about sql union/join but this is a special case because I would like to compare the expenses of each customer in two different stores. I'm using the "Sakila" sample database

SELECT customer.last_name AS prenom,
       sum(payment.amount) AS total_payé
FROM payment
INNER JOIN customer ON payment.customer_id = customer.customer_id
WHERE store_id = 1
LIMIT 3
UNION ALL
SELECT customer.last_name AS prenom,
       sum(payment.amount) AS total_payé
FROM payment
INNER JOIN customer ON payment.customer_id = customer.customer_id
WHERE store_id = 2
LIMIT 3

but have a error, i need help :(

CodePudding user response：

I dont think this is really what you intend. The limit will always just give whatever happens to be the first 3 (in this case) returned. No guarantee that a person shopping in store 1 buys from store 2.

You might be best with a sum( case ) condition, something like

SELECT 
        c.last_name prenom,
        c.customer_id,
        sum( case when p.store_id = 1 then p.amount else 0 end ) Store1Paid,
        sum( case when p.store_id = 2 then p.amount else 0 end ) Store2Paid
    FROM 
        payment p
            JOIN customer c
                ON p.customer_id = c.customer_id
    WHERE 
        p.store_id in( 1, 2 )
    GROUP BY
       c.customer_id,
       c.last_name 
    LIMIT 
        3

Now, if you are more concerned with who are the highest paying customers from each respective store, then, yes a union would be more practical, but be sure to include which store they are from so you know in your result set, not just 3 people from each

SELECT 
        c.last_name prenom,
        c.customer_id,
        p.store_id,
        sum( p.amount ) StorePayments
    FROM 
        payment p
            JOIN customer c
                ON p.customer_id = c.customer_id
    WHERE 
        p.store_id = 1
    GROUP BY
        c.customer_id,
        c.last_name,
        p.store_id
    order by
        sum( p.amount ) desc
    LIMIT 
        3
union ALL
SELECT 
        c.last_name prenom,
        c.customer_id,
        p.store_id,
        sum( p.amount ) StorePayments
    FROM 
        payment p
            JOIN customer c
                ON p.customer_id = c.customer_id
    WHERE 
        p.store_id = 2
    GROUP BY
        c.customer_id,
        c.last_name,
        p.store_id
    order by
        sum( p.amount ) desc
    LIMIT 
        3

The reason I included the customer ID within all the group by clauses, what if you had 10 people all the same name "John". You would not be differentiating between the individual customer by their ID.