Personalize Yedda, (And make Danny Happy)
People ask & answer about almost everything. Tell us what you're interested in... So we can personalize Yedda especially for you
I'm interested in:
Save
Cancel

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
Share Send to a friend Watch Report
 
 

5 Posted Answers
Order by

 
51 answers
37 helpful answers

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 2006-12-25T20:56:08Z
Helpful?(1)
Rated #2 out of 5
 
29 answers
12 helpful answers

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 2006-12-26T13:03:17Z
Helpful?(0)
Rated #3 out of 5
 
33 answers
18 helpful answers

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 2006-12-26T18:44:42Z
ravidor was invited by Yedda to answer this question.

Helpful?(5)
Rated as Best Answer
 

how to write a query to get different data from two tables
Posted 2008-05-02T14:15:43Z
Helpful?(0)
Rated #4 out of 5
 

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 2008-07-07T05:50:56Z
Helpful?(0)
Rated #5 out of 5

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

Feed - Subscribe to changes to this Q&A Blog
ADVERTISEMENT
  • Answers
  • Web
Copyright © 2006-2009, Yedda Inc. and respective copyright owners · CC License