Question about Count (*)

How to include a Count?
I have a table A, B C
TableA TableB TableC
idA idA idA
I need to query against the TableB where TableB.dt_Date = "date"
returns me the all fields including idA.
Here its fine.
But, I want to know how many idA related with the results from the query I
will find in the TableC.
take a look:
for instance
results from my query...
idA name total ( I need this field total)
18 nondn 2
239 djdjjd 4
383 djdjdj 5
382 test
Where total is the total idA I will find on the TableC, for instance above
idA 18 has 2
on tableC
Any help?
thanks in advance!Luiz wrote:
> How to include a Count?
> I have a table A, B C
> TableA TableB TableC
> idA idA idA
> I need to query against the TableB where TableB.dt_Date = "date"
> returns me the all fields including idA.
> Here its fine.
> But, I want to know how many idA related with the results from the query I
> will find in the TableC.
> take a look:
> for instance
> results from my query...
> idA name total ( I need this field total)
> 18 nondn 2
> 239 djdjjd 4
> 383 djdjdj 5
> 382 test
> Where total is the total idA I will find on the TableC, for instance above
> idA 18 has 2
> on tableC
Your question is irritatingly vague and hard to understand. But perhaps
this is what you mean:
SELECT TableB.idA, TableB.name, COUNT(TableC.idA) AS total
FROM TableB
INNER JOIN TableC ON TableB.idA = TableC.idA
WHERE TableB.dt_Date = "date"|||It sounds like you're just needing the syntax (though you half posted
it in your title)
Select idA, [name], Count(*) as Total
FROM TableC
Group By idA, [Name]
Cheers
Will|||Will your example parse rpresser? I would have thought you'd need a
group by clause.|||I'll try to be clear...
more simple:
I select from table Cinema using a criteria whatever.
so, I will get a list
CinemaNome IDCinema
djdjdjjd 19
dkdkdk 20
sksks 23
Thats fine. But I have another TableB. who has n IDCinema
I want to join a count from the tableB based on the results.
TableB
ID IDCInema
1 19
2 19
3 20
3 23
N 23
like this:
CinemaNome IDCinema Total
djdjdjjd 19 2
dkdkdk 20 1
sksks 23 2
I hope its clear.
Thanks in advance!
"rpresser" wrote:

> Luiz wrote:
> Your question is irritatingly vague and hard to understand. But perhaps
> this is what you mean:
> SELECT TableB.idA, TableB.name, COUNT(TableC.idA) AS total
> FROM TableB
> INNER JOIN TableC ON TableB.idA = TableC.idA
> WHERE TableB.dt_Date = "date"
>|||As will pointed out, this query needs a group by clause, otherwise what
rpresser posted should work, if I understand your post correctly.
SELECT TableB.idA, TableB.name, COUNT(TableC.idA) AS total
FROM TableB
INNER JOIN TableC ON TableB.idA = TableC.idA
WHERE TableB.dt_Date = "date"
group by TableB.idA, TableB.name|||Hi Jim,
thanks, it works with two tables!
I want to add one more table.
because the "date " is on tableA, and the tableA also has the "IDCinema".
if you have any suggestion...comments.
Thanks!
"Jim Underwood" wrote:

> As will pointed out, this query needs a group by clause, otherwise what
> rpresser posted should work, if I understand your post correctly.
> SELECT TableB.idA, TableB.name, COUNT(TableC.idA) AS total
> FROM TableB
> INNER JOIN TableC ON TableB.idA = TableC.idA
> WHERE TableB.dt_Date = "date"
> group by TableB.idA, TableB.name
>
>|||just add an inner join on table A, and change the tableb.dt_date to
tablea.dt_date.
INNER JOIN TableA ON TableB.idA = TableC.idA
Also, check out BOL for more information on joins, or try these sites..
http://www.w3schools.com/sql/sql_intro.asp
http://sqlzoo.net/
"Luiz" <Luiz@.discussions.microsoft.com> wrote in message
news:F94EAF81-3699-4820-A930-EBA59948B549@.microsoft.com...
> Hi Jim,
> thanks, it works with two tables!
>
> I want to add one more table.
> because the "date " is on tableA, and the tableA also has the "IDCinema".
> if you have any suggestion...comments.
> Thanks!
> "Jim Underwood" wrote:
>|||Will wrote:
> Will your example parse rpresser? I would have thought you'd need a
> group by clause.
Oops. Haste makes waste.
It does indeed need a group by clause.