I have three tables ta,tb,tc.

Fields in ta:  id,name
Fields in tb:  id,taid,qty
Fields in tc:  id,taid,pin

Now I need the results as follows,

sum(tb.qty), count(tc.taid)

Both I should retrieve in single SQL query, not more than one.
I use mySQL database.

Thanks

---

Clarification of Question by nkans-ga on 27 Sep 2002 19:29 PDT

taid <--- belongs to 'id' field in ta

Also that query should give

sum(tb.qty) , count(tc.taid) with respect to every 'id' in table ta

Hi nkans-ga,

The SQL query you are looking for is as follows:


SELECT ta.Name, SUM(tb.qty) AS QtySum, COUNT(tc.taid) AS Counter
FROM ta, tb, tc
WHERE ta.id = tb.taid AND ta.id = tc.taid

I've added in the ta.Name column because you probably need to know
which entry in table A each set of count and sum figures correspond
to.

Cheers!

answerguru-ga

---

Request for Answer Clarification by nkans-ga on 27 Sep 2002 20:04 PDT

That is not working answerguru.
I tried with that one first then only i came to google.

Please check your answer.

---

Clarification of Answer by answerguru-ga on 27 Sep 2002 22:06 PDT

My apologies...but before I could provide the remedy it appears that
justask-ga has come up with the solution that works.

answerguru-ga

---

Request for Answer Clarification by nkans-ga on 27 Sep 2002 22:16 PDT

That  also doesnot works.
Can you provide me the solution for the same.

Or else you can allow other researchers to answer for this.

---

Clarification of Answer by answerguru-ga on 27 Sep 2002 22:29 PDT

I unfortunately can't release the question, its out of my hands.
Considering the price I must (respectfully) remind you that most
questions at this price level will not get answered at all. Sorry
about that.

If you would like to draw more attention to your question, your best
bet is to post another question with a higher value so another
researcher will be motivated to answer your question.

If there are any other researchers who would like to give this
question a crack please feel free to do so...

---

Request for Answer Clarification by nkans-ga on 30 Sep 2002 21:20 PDT

Thanks. Your answer is not suitng my problem exactly.

Thanks for your time.

The researcher is not able to give an answer.
I request you to refund my paid amount.

Kannaiyan

Hello, nkans-ga!

Did you try the following query (assuming every row in tb and tc has
different id):

SELECT ta.Name, SUM(tb.qty)/COUNT(DISTINCT tc.id) AS QtySum,
COUNT(tc.taid)/COUNT(DISTINCT tb.id) AS Counter
FROM ta, tb, tc 
WHERE ta.id = tb.taid AND ta.id = tc.taid
GROUP BY ta.Name

I like your question :)

'tb' has 'taid' in its table where it will repeat.
'tc' has 'taid' in its table where it will repeat.

I tried what justask given, no hope for the problem, yet remain unsolved.

It seems like there may be inconsistancies in your data which yields
an incorrect result...I just tried the previous query and it works
fine. Perhaps there is a subtle point about mySQL in particular that
we are ignoring?

I'm sorry answerguru.

The answer provided by justask is alright only with certain condition
where I don't have that one. The taid were same in my condition.

With respect to the amount for this question, Did google asked you
give a wrong answer if that founds cheap?

I'm sorry I can pay only the affordable amount not the amount which
you suggest to me.