SQL query that takes data from two tables

I think it should be a simple thing to do in SQL, but I don't know much about it and my boss is out so I can't ask him:

suppose I have a "clients" table and an "orders" table. Every order belongs to a client. How do I write a query that finds certain users in the "clients" table and counts how many order each of these clients made in the "orders" table? For example, how many orders all the clients from South Africa made, when the country is in the "clients" table.

Thanks in advance. 


Answer this question
  • # Posted 2 years ago
  • 3984 views
Share Send to a friend Watch Report
 
 

Posted Answers

Order by
 
Roki
50 answers
100 thumbs up

if i got you right, it should be something like this:

select count(*),clients.country from clients inner join orders on clients.id=orders.clientid group by clients.country

it joins the two tables by the client's ids and counts how many rows for each country (by the count(*) and then the group sentence).
Posted 2 years ago ( permalink )
In reply to Matthew's question
Rated as
#2 out of 5
0
1
  Report Abuse  

Helpful?

line
line
line



 
Matthew
27 answers
17 thumbs up

Thanks for your answer Roki, your query works but it doesn't do what I wanted it to do, I guess I wasn't clear in my question. I want

- To find all the clients from South Africa in the "clients" table.

- For each of these clients, to find how many orders he made.

Thanks.

Posted 2 years ago ( permalink )
In reply to Roki's answer
Rated as
#3 out of 5
0
0
  Report Abuse  

Helpful?

line
line
line



 
ravidor
32 answers
28 thumbs up

Born. Having the time of my life. Will die.

SELECT
    clients.id,
    COUNT(orders.id)
FROM
    clients JOIN orders ON (clients.id = orders.clientid)
AND    clients.country = 'South Africa'
GROUP BY
    clients.id
Posted 2 years ago ( permalink )
In reply to Matthew's question
ravidor was invited by Yedda to answer this question.

Rated as
Best Answer
0
5
  Report Abuse  

Helpful?

line
line
line



 

how to write a query to get different data from two tables
Posted 7 months ago ( permalink )
In reply to Matthew's question
Rated as
#4 out of 5
0
0
  Report Abuse  

Helpful?

line
line
line



 

two tables (state, city) having common  field (state_id) i want to display column city and state from these tables on the basis of state_id
Posted 5 months ago ( permalink )
In reply to Matthew's question
Rated as
#5 out of 5
0
0
  Report Abuse  

Helpful?

line
line
line



Sign in to participate

Got an answer for Matthew? Would you like to comment on the posted answers, or vote for the one which you think is the best?

Sign up for a free account, or sign in (if you're already a member).

Explore Related Questions

Other people asked questions on similar topics, check out the answers they received:


How do I protect my self from sql injuction ...

How do I protect my self and my site from sql injuction?
Submitted by del2 2 years ago
  • 1 answer
  • viewed 697 times

Last answer posted 2 years ago by leonid