SQL: how to get more information from a GROUP BY statement

Question

Consider the following SQL statement:

SELECT 
invoice.customer_name, 
invoice.customer_id, 
invoice.shop_location,
sum(invoice.cost)
FROM invoice
GROUP BY
invoice.customer_name, invoice.customer_id, invoice.shop_location

What can be done to the statement to identify which rows would be identical to other rows if it weren't for their invoice.shop_location value?

Answer 1

Try this - Removing Invoice.ShopLocation

SELECT  
invoice.customer_name,  
invoice.customer_id,  
sum(invoice.cost) 
FROM invoice 
GROUP BY 
invoice.customer_name, invoice.customer_id

This gives you all rows which match rest of the fields

Answer 2

Select customer_name, Customer_id, count(Shop_location) from
(
    SELECT  
        invoice.customer_name,  
        invoice.customer_id,  
        invoice.shop_location, 
        sum(invoice.cost) 
    FROM invoice 
    GROUP BY 
        invoice.customer_name, 
        invoice.customer_id,
        invoice.shop_location 
 )
 Group by customer_name, Customer_id
 Having    count(Shop_location) > 1

The above query will give you the combination of Customername and id who have more than one shop location

Note : I'm not sure whether this is optimised