How to fetch data from multiple tables in SQL

Question

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:

Order table:

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

Tip of today: Switch to modern, explicit JOIN syntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed. — jarlh
– jarlh, Commented Feb 18, 2019 at 12:47

forpas · Accepted Answer · 2019-02-18 13:06:06Z

1

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

edited Feb 18, 2019 at 13:06

answered Feb 18, 2019 at 13:00

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Fahmi · Accepted Answer · 2019-02-18 12:52:33Z

0

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.

edited Feb 18, 2019 at 12:52

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

answered Feb 18, 2019 at 12:47

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

1 Comment

M. Usaid Over a year ago

Error in syntax :(

Collectives™ on Stack Overflow

How to fetch data from multiple tables in SQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related