CODEWITHSUNDEEP
sqlgroup-by
CJ7
CJ7

Reputation: 23285

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

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?

Upvotes: 0

Views: 159

Answers (2)

The King
The King

Reputation: 4650

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

Upvotes: 1

Sachin Shanbhag
Sachin Shanbhag

Reputation: 55499

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

Upvotes: 2

Related Questions