MySql Count clause with where [duplicate]

Question

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

What does "doesn't seem to work," mean? Is there an error message? — LegendofPedro
– LegendofPedro, Commented Oct 20, 2019 at 16:37
"You have an error in your mysql syntax please consult the manual..." not a very helpful error message. — Rni224
– Rni224, Commented Oct 20, 2019 at 16:43
Does select count() as cnt from payments where amount >= 1000; work? — LegendofPedro
– LegendofPedro, Commented Oct 20, 2019 at 16:45
your table seems not related .. you have not condtion for join the orders table to the payments table .. — ScaisEdge
– ScaisEdge, Commented Oct 20, 2019 at 16:46

ScaisEdge · Accepted Answer · 2019-10-20 16:46:04Z

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);

answered Oct 20, 2019 at 16:46

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

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

2 Comments

Rni224 Over a year ago

Thank you for the clarification, table above doesn't work but I worked something out with inner join.

ScaisEdge Over a year ago

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

Collectives™ on Stack Overflow

MySql Count clause with where [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related