Two counts in one query

[Senna]

(Access)
Hi,
I have three tables looking something like this:
code:-----------------------------------------------------
CREATE TABLE tabl1
(
t1_id int counter
t1_title varchar
)

CREATE TABLE table2
(
t2_id int counter
t2_t1_id int
t2_title varchar
)

CREATE TABLE table3
(
t3_id int counter
t3_t2_id int
t3_title varchar
)
end code:-------------------------------------------------

What I would like to do is. In one query count how many
t2_id and t3_id(thru table2) that belong to each t1_id.

I have a query that do the first part, counts t2_id, see
below. But I dont know how to get the second count in
there. Help please.

code:-----------------------------------------------------
SELECT t1_id, t1_title, (SELECT COUNT(t2_id) FROM table2
WHERE t2_t1_id = t1_id) as total2 FROM table1
end code:-------------------------------------------------

 

[Tom Ellison]

Dear Senna:

Let's build the complete, joined strucutre, then aggregate:

Query1:

SELECT T1,t1_id, COUNT(*) As t2Count
FROM table1 T1
INNER JOIN table2 T2 ON T2.t2_t1_id = T1.t1_id
GROUP BY T1.t1_id

This is an alternative way to get the same result as you current one.

Query2:

SELECT T1.t1_id, COUNT(*) AS t3Count
FROM table1 T1
INNER JOIN table2 T2 ON T2.t2_t1_id = T1.t1_id
INNER JOIN table3 T3 ON T3.t3_t2_id = T2.t2_id
GROUP BY T1.t1_id

Now you can put it all together.

Query3

SELECT Q1.t1_id, Q1.t2Count, Q2.t3Count
FROM Query1 Q1
INNER JOIN Query2 Q2 ON Q2.t1_id = Q1.t1_id

Does this do it for you?

I considered doing this with another subquery, extending what you had,
but it's a bit complex. I was concerned that if you are using Jet,
that may not work. If you're using MSDE or SQL Server, we could try
that, too.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Senna]

Hi Tom,

You answer first went a litte over my head. But after a
little testing of your queries I understood it and it
work perfect. A big thank you.

Best regards / Senna

 

[Guest]

Hi again,

If its not to much to ask I really would like to see the
complexed version. For learning reasons.

Best regards / Senna

 

[Tom Ellison]

Dear Senna:

Well, here goes then. As I said, this may be something that won't
work for Jet, but it should work for MSDE and SQL Server.

There sill be another subquery to give the values from table3. This
subquery starts out just returning the values for each row in table2,
just like your original returned the rows of table2 for each row in
table1:

SELECT t1_id, t1_title, (SELECT COUNT(t2_id) FROM table2
WHERE t2_t1_id = t1_id) as total2 FROM table1

SELECT T2.t2_id, (SELECT COUNT(*)
FROM table3 T3
WHERE T3.t3_t2_id = T2.t2_id)
FROM table2 T2

This is very much like the query you wrote initially. Next, it has to
be adapted to return the count of all the rows in table3 for all the
rows in table2 that relate to the current row from table1:

SELECT T1.t1_id,
(SELECT SUM(t3Count)
FROM (SELECT (SELECT COUNT(*) t3Count
FROM table3 T3
WHERE T3.t3_t2_id = T2.t2_id)
FROM table2 T2 WHERE T2.t2_t1_id = T1.t1_id) t3Count
FROM table1 T1

For me, this is a little tough to just quote off the cuff without
testing it from the inside out, but the ideas I intended are there.
You could add the subquery for the count from table2, as you had
originally. I left that out since it is already known and I thought
it less confusing this way.

Again, I have some doubts this would work in Jet, but (if I haven't
made any mistakes) it is close to what to write for MSDE and SQL
Server.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Senna]

Hi Tom,

Will test it out and see if it work and produce the same
output. Thanks for taking your time and all your help.

Best regards / Senna