CODEWITHSUNDEEP
mysqlsql
Brettski
Brettski

Reputation: 1051

A cleaner way to do this SQL query

So have a list of customers, that have a list of customers.

I have 2 tables, customers and invoices.

Customer_id can be the same for my customers e.g

Client A

Customer #1

Client B

Customer #1

So invoice table can have customer_id of 1, for different customers.

The below query works, but seems a little messy. Any ideas to clean it up?

  SELECT   name, 
           sum(sub_total) total
    FROM   customers, invoices
   WHERE   customer_id = 1438 and 
           invoices.customer_id = 1438 and 
           customers.customer_id = invoices.customer_id 
GROUP BY   name 
Order By   total DESC;

Upvotes: 0

Views: 197

Answers (3)

DRapp
DRapp

Reputation: 48139

I would strongly NOT do a group by on name... Say you have two customers named "Bill Smith", but one is customer ID 10, and the other is customer ID 785... You just combined them into a single person. You SHOULD be grouping by ID, not the name...

Now, that said, you are querying for only a single customer ID anyhow and will only ever return a single record. If what you are really trying to accomplish is getting ALL customers and the tot of their respective invoices, remove the where clause of the single customer. You can keep the group by ID, but show the actual customer's name. If you have multiple customers with the same total, you can sub-sort by their name, but the grouping is STILL based on just the customer's ID.

SELECT
      c.name, 
      sum(i.sub_total) total
   FROM
      customers
         JOIN invoices
            on c.Customer_ID = i.Customer_ID
   GROUP BY
      c.customer_ID 
   Order By
      total DESC,
      c.Name

Upvotes: 1

gdoron
gdoron

Reputation: 150263

If A = B and A = 1438 then B = 1438 you don't need to check it...
(Trust me on this one, I got 60+ in math in highschool)

SELECT   name, sum(sub_total) total    
FROM     customers, invoices    
WHERE    invoices.customer_id  = 1438
AND      customers.customer_id = invoices.customer_id 
GROUP BY name 
ORDER BY total DESC;

Or with explicitly saying which type of JOIN you want:

SELECT   name, sum(sub_total) total    
FROM     customers  INNER JOIN invoices 
ON       customers.customer_id = invoices.customer_id 
WHERE    invoices.customer_id  = 1438
GROUP BY name 
ORDER BY total DESC;

Upvotes: 4

Randy
Randy

Reputation: 16677

you dont need the extra check...

SELECT name, sum(sub_total) total
FROM customers, invoices
WHERE 
customer_id = 1438 and 
customers.customer_id = invoices.customer_id 
GROUP BY name order by total DESC;

Upvotes: 1

Related Questions