problem with SQL query using COUNT() function

Question

0.00/5 (No votes)

See more:

I have the following SQL query:

SQL

SELECT DISTINCT `c1` FROM `table1` AS Column1 INNER JOIN `table2` ON `table1`.`c2`=`table2`.`c2`

Above query returns some distinct rows from `table1`.
Now for each row(value of c1 column) returned by the above query, I want to count the rows from `table2` which have column `c1` value equal to to the above query returned value. for example, for a single row returned by the above query, I have a returned value 'A'.

Now the single query for this row would be something like this:

SQL

SELECT COUNT(*) FROM `table2` WHERE `table2`.`c1` = A

Now, being new to SQL, my question is, How can I mix both of the above queries in one single query.
In a way that,for example, in a row returned by the final(mixed) query,I have a column returning 'A' value and another column returning the number of rows of `table2` having column `c1` with value of 'A'?

I hope I have explained my question clearly.

Any help with this problem would be highly appreciated.

Thanks in advance.

Posted 28-Jan-15 0:18am

Member 11033015

Add a Solution

2 solutions

Solution 2

Hope this will do:

SQL

SELECT COUNT(table2.C2) FROM table2
INNER JOIN table1 ON table2.C2= table1.C2
GROUP BY table2.C2
WHERE table2.c1 = A

Further, have a look on below link for more detailed queries:
http://stackoverflow.com/questions/17751393/sql-query-with-join-count-and-where[^]

Thanks.

Posted 28-Jan-15 0:44am

Snesh Prajapati

Updated 28-Jan-15 0:50am

v2

Comments

Member 11033015 28-Jan-15 6:57am

Thanks for your answer Snesh, but what is the meaning of 'A' here? In my example, 'A' stands for a returned value of a single row by the first query and I think it should not be in the mixed query.

Snesh Prajapati 28-Jan-15 7:13am

Got it....will think and re-write my query.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Reiss · Accepted Answer · 2015-01-28T00:36:00

Solution 1

I think this is what you are after

SQL

select
t1.Column1 
,count(*) 'Count'
from
TableA t1
inner join
TableB t2
on
t1.Column1 = t2.Column1
group by
t1.Column1

Posted 28-Jan-15 0:36am

Reiss

Comments

Member 11033015 28-Jan-15 7:38am

Thanks for your answer Reiss, This is exactly what I was after. But I think you should swap "FROM" clause TableA t1 with "INNER JOIN" clause TableB t2 according to my question. Thanks anyway.