Counting with 3 tables 1

[dvannoy]

I have the below query with an inner join that works like I want. But, I now need to join a third table and get a count of a specific field.

SELECT

TABLE1.FIELD1
,TABLE2.FIELD1
,TABLE2.FIELD2
,TABLE1.FIELD2
,COUNT(TABLE1.FIELD3) AS QTY

FROM            

TABLE2

INNER JOIN
TABLE1
ON TABLE2.FIELD = TABLE1.FIELD

I need to add a third table and count a field.

any help would be appreciated

thanks

 

[johnherman]

How does TABLE3 relate to TABLE1 and/or TABLE2. You can continue your INNER JOIN in a manner similar to this:

SELECT

TABLE1.FIELD1
,TABLE2.FIELD1
,TABLE2.FIELD2
,TABLE1.FIELD2
,COUNT(TABLE1.FIELD3) AS QTY
,COUNT(TABLE3.FIELDx) AS THE_COUNT
FROM TABLE2

INNER JOIN
TABLE1
ON TABLE2.FIELD = TABLE1.FIELD

INNER JOIN TABLE3
ON TABLE3.FIELD = TABLE1.FIELD (or however TABLE3 is related to the others)

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[dvannoy]

Thanks,

when I do that it's counting all the records within those joins

 

[TamarGranor]

You need to tell us more about exactly what it is you're trying to get. How do the tables relate? Which records are you trying to count?

Tamar

 

[dhookom]

dvannoy,
Are you hoping to count distinct values?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Olaf Doschke]

Even in the original query COUNT(TABLE1.FIELD3) AS QTY does not count distinct field3 values, it counts nonnull values, also duplicates.

Also, COUNT always acts on the whole result, not on the separate tables. If you want to join a count done on the separate table3 you could do a subquery SELECT FOREIGNKEY, COUNT(FIELD) FROM TABLE3 GROUP BY FOREIGNKEY as SUBQUERY and JOIN SUBQUERY on the foreign key.

For example you could

SELECT ORDERS.*, ORDERITEMCOUNT.ItemCount 
FROM ORDERS 
LEFT JOIN (SELECT ORDERID, COUNT(*) as ItemCount FROM ORDERITEMS GROUP BY ORDERID) ORDERITEMCOUNT 
ON ORDERS.ID = ORDERITEMCOUNT.ORDERID

The more usual way would be to simply join ORDERITEMS and apply grouping on the overall result.

So, what do you expect to count?

Edit: To once more use the orders and order items as example, a query like the following will count as the result field names advertise

SELECT COUNT(DISTINCT ORDERS.ID) as OrderCount
COUNT(ORDERS.ID) as OrderstimesOrderitemsCount
COUNT(*) as OrderstimesOrderitemsCount
FROM ORDERS 
LEFT JOIN ORDERITEMS
ON ORDERS.ID = ORDERITEMS.ORDERID

As ORDERS.ID is never null, you have no difference between COUNT(*) and COUNT(ORDERS.ID) here. I think you expect COUNT(expression) to act as the DISTINCT count but that needs COUNT(DISTINCT expression).
It's unclear whether you want the single rows that will be added, when you join a table3 or whether you only want to join it for a count, then you might use the subquery approach to first compute the count and join only that as 1:1 related data not growing your overall result row count. COUNT(DISTINCT ORDERS.ID) then also is not a solution, it still multiplies all ORDERS rows as many times as each order has order items. Just like the join copies all ORDERS data of the same order id as many times as there are orderitems to be able to add orderitem detail data, the COUNT(DISTINCT ORDERS.ID) is also repeated orderitems times.

I think you're not very familiar with SQL, neither with how JOINS act, how COUNT acts and how to GROUP BY. So as Tamar asked already, what are you trying to achieve?

Bye, Olaf.

 

[dvannoy]

so, here is what I have so far

SELECT document_number,  
       qty = (SELECT COUNT(O.document_number)
	   FROM TABLE1 O 
	   WHERE O.document_number= C.document_number)
  FROM TABLE2 C

This gets me the counts I'm looking for but I now need to be able to select more fields from TABLE1.
I've tried using a left join within the query after "FROM TABLE2 C" but keep getting expression and aggregate errors.

 

[johnherman]

How about simply this:

SELECT C.document_number,
qty = (SELECT COUNT(O.document_number)
FROM TABLE1 O
WHERE O.document_number= C.document_number),
C.next_field,
C.last_field
FROM TABLE2 C


==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[Olaf Doschke]

How about this, quite as you had it initially:

SELECT C.document_number,  
       qty = (SELECT COUNT(O.document_number)
	   FROM TABLE1 O 
	   WHERE O.document_number= C.document_number),
       D.*
  FROM TABLE2 C 
  INNER JOIN TABLE1 D
  ON C.FIELD = D.FIELD

Notice: Once you give a table an alias name like C, you have to use C to address it, also in WHERE, ON, GROUP BY clauses...

Bye, Olaf.

 

[dvannoy]

Olaf, that worked. thank you so much. appreciate it

 

[Olaf Doschke]

Maybe also learn from this:

http://www.sqlteam.com/article/counting-parents-and-children-with-count-distinct

I don't see what you're struggling with, as you don't show the failing queries.

Bye, Olaf.