Skip to main content
A
Anonymous
November 7, 2012
Question

How do you join tables on a COUNT query

  • November 7, 2012
  • 2 replies
  • 1595 views

I have a basic count select statment that pulls back the top occurances of a certain pid in a table.  I am trying to join that table with another table to pull back the details about that pid but I can't seem to get it to work.  Would it be better to use union.

This is my existing query

SELECTpid, COUNT(pid) AS pcount

FROM Table1

GROUP BY pid

ORDER BY pcount DESC

The basic structure doesn't work..

SELECT Table2.name, Table1.pid, COUNT(pid) AS pcount

FROM Table1, Table2

WHERE Table1.pid = Table2.pid

GROUP BY propertyid

ORDER BY pcount DESC

Does anyone have advice on how to strutcure this... a certain join or a union.  It could be a LOT of records so I don't want to start this with the wrong structure.

Thank you!

This is ColdFusion 10 on MS SQL

This topic has been closed for replies.

2 replies

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
November 7, 2012

Some modifications:

SELECT Table2.name, Table1.pid, COUNT(Table2.pid) AS pcount

FROM Table1, Table2

WHERE Table1.pid = Table2.pid

GROUP BY Table2.pid

ORDER BY pcount DESC

A
Anonymous
November 7, 2012

I did try this but got "name is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." but I had to switch the table order because I want the count from Table 1.  I did an outline with more details above. 

Thank you for your quck response.  The Cold Fusion community is great.

D
Dan_Bracuk
Inspiring
November 7, 2012

When you are selecting a combinations of database fields and aggregates such as count(), min(), sum(), etc, the following rules apply.

1.  You need a group by clause.

2. The group by clause has to contain every field in the select clause.

3.  The group by clause can contain only those fields in the select clause.

Your original query did not follow rules 2 and 3.

Carl Von Stetten
Carl Von Stetten
Legend
November 7, 2012

I have a couple of ideas, but before I make suggestions, can you show the structure of both Table1 and Table2 (what columns are in each table).

Also, to clarify what you need - you are looking to pull back the count of occurrences of each "pid" in Table1, and join that to the name for each "pid" (and the name can be found in Table2)?

-Carl V.

A
Anonymous
November 7, 2012

Yes, I want to pull back the number of occurances in Table 1. 

Table1 has a basic struture of (pid, createdate) where it just records the occurances.

Table 2 has details about the pid such as (pid, name, description, price)

It is basically a most viewd item query.  So I want to pull back the details from Table 2 where the most incidents of Table 1 exist for a top ten list.  Is that the info that would help?

Thanks

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices