CODEWITHSUNDEEP
mysqlsql
M. Usaid
M. Usaid

Reputation: 70

How to fetch data from multiple tables in SQL

create table customers
    (
    custID int(11) primary key, 
    customerName varchar(50),
    phone varchar(50),
    address varchar(50),
    state varchar(50),
    postalCode varchar(15),
    country varchar(50),
    creditLimit Decimal(10,2)
    );

create table product
(
    productID varchar(15) primary key,
    productName varchar(70),
    productVendor varchar(50),
    productDescription text,
    qtyInStock smallint(6),
    price Decimal(10,2)
);

create table orders
(
    orderNumber int(11) primary key,
    orderDate date,
    requiredDate date,
    shippedDate date,
    status varchar(15),
    comments text,
    custID int(11),
    salesID int(11),
    foreign key(custID) references customers(custID),
    foreign key(salesID) references salespersonnel(employeeID)
);

here are three tables, i want to fetch all of the orders of one particular customer and their total money spent.

I have two entries in order table and payment table

Payment Table: enter image description here

Order table: enter image description here

I am executing this query 

select count(p.orderNumber) as 'Total Orders', sum(amount) as 'Amount Spent'
from orders p,
     payments q
where p.custid = q.custid
  and q.custid = 1;

This query is generating double result that means total of 2200 must be returned as the total amount spent, but i am getting 4400 and total 2 orders are placed by customer, but i am getting 4. Please help

Upvotes: 1

Views: 1660

Answers (2)

forpas
forpas

Reputation: 164069

You need to join the 2 tables on the ordernumber column:

select 
  count(o.orderNumber) as 'Total Orders', 
  sum(p.amount) as 'Amount Spent'
from orders o inner join payment p
on p.ordernumber = o.ordernumber
where o.custid = 1

See the demo If you want to get these sums for every customer:

select
  o.custid,
  count(o.orderNumber) as 'Total Orders', 
  sum(p.amount) as 'Amount Spent'
from orders o inner join payment p
on p.ordernumber = o.ordernumber
group by o.custid

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269553

You need to aggregate before joining. Otherwise, you will get a Cartesian product (and the wrong results as you see):

select o.custid, o.Total_Orders, p.Amount_Spent
from (select o.cust_id, count(*) as total_orders
      from orders o
      group by o.custid
     ) o left join 
     (select p.cust_id, sum(amount) as amount_spent
      from payments p
      group by p.cust_id
     ) p
     on p.custid = o.custid ;

You can add a WHERE clause to filter the results. You can do it in the outer query or in the subqueries. The latter is better from a performance perspective.

Notes:

  • Never use commas in the FROM clause. Always use proper, explicit, standard JOIN syntax.
  • Use meaningful table aliases. p for orders instead of payments is just confusing.
  • Only use letters, digits, and underscore in column aliases, so they do not have to be escaped.

Upvotes: 0

Related Questions