1

I am trying to count the number of 'Shipped' orders with an amount greater than 1000 from two tables.

I have table Orders(Status column) and table Payments(Amount column)

select count(o.status, p.amount)
from orders o, payments p
where (o.status = 'Shipped' AND p.amount>1000);

After getting an error I broke up the problem into two with the first finding all 'Shipped' status in Orders. This works just fine. 

select count(o.status)
from orders o 
where o.status = 'Shipped';

But trying to count all payments above 1000 doesn't seem to work. 

select count(p.amount)
from payments p 
where p.amount>=1000;

In case anyone needs the file, I am using the sample DB from MySQl sample database http://www.mysqltutorial.org/mysql-sample-database.aspx

Rni224
  • 15
  • 2

1 Answers1

1

if you want know the number of order "shipped" with payment with (each) amount > 1000 the first you should join the table in proper way
looking to your schema the order table is realated to payments table via the customers on customerNumber

so you should 

  select count(distinct o.id)
  from orders o
  INNER JOIN customers c ON c.customerNumber =  o.customerNumber
  INNER JOIN  payments p p.customerNumber = c.customerNumber
  where o.status = 'Shipped' 
  AND p.amount>1000);
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • Thank you for the clarification, table above doesn't work but I worked something out with inner join. – Rni224 Oct 20 '19 at 16:55
  • what do you mean wih "table above doesn't work " .. you have an error .. show me the exact error message ... wrong result .. update your question add a clear data sample as tabular tex (not a link to a tutorial) and your expected result – ScaisEdge Oct 20 '19 at 16:56