CODEWITHSUNDEEP
sqlsql-serverselectssms
Aksel482
Aksel482

Reputation: 46

SQL Server : select price of all orders made by the customer

Basically, I have two SQL Server tables, Customer and Orders. On a per customer basis, I want to display customer's information and the price of all orders made by the customer.

Customer table:

Customer Table

Orders table:

enter image description here

Database diagram:

Diagram

This is my select function to get the price of all orders made by the customers.

select 
    Customer.CustomerID, 
    Customer.CustomerName, 
    Customer.CustomerAdress, 
    format(sum(Orders.Debit-Orders.Credit),'c', 'tr-TR') as Balance 
from 
    Customer 
join 
    Orders on Customer.CustomerID = Orders.CustomerID 
           and Customer.CustomerName = Orders.CustomerName 
group by 
    Customer.CustomerID, 
    Customer.CustomerName,   
    Customer.CustomerAdress

It works fine. However, newly created customers don't have any orders yet, and this select doesn't return newly created customers.

I want to merge and display the customers who don't have any orders and set their balance as zero.

Upvotes: 0

Views: 491

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

If you want to keep all rows in one table, then you want an outer join or correlated subquery.

I also want to point out the use of table aliases to simplify the writing and reading of queries:

select c.CustomerID, c.CustomerName, c.CustomerAdress, 
       format(sum(o.Debit - o.Credit), 'c', 'tr-TR') as Balance
from Customer c join
     Orders o
     on c.CustomerID = o.CustomerID and 
        c.CustomerName = o.CustomerName
group by c.CustomerID, c.CustomerName, c.CustomerAdress;

Storing the CustomerName in the orders table seems suspicious. I would expect CustomerId to be the primary key of Customers. That would make that name redundant at best and a source of error in most cases.

Upvotes: 0

Related Questions