How do you join tables on a COUNT query

[Guest]

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

[Carl Von Stetten]

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.

[Guest]

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

[BKBK]

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

[Guest]

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.

[Dan_Bracuk]

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.

[Guest]

Is it possible to use UNION or some other JOIN to bring these together?  I'm trying to bring in data from a second table but the count comes from a table that only tracks the id. 

[Carl Von Stetten]

TheUrbanBrain wrote:

Is it possible to use UNION or some other JOIN to bring these together?  I'm trying to bring in data from a second table but the count comes from a table that only tracks the id. 

The WHERE Table1.pid = Table2.pid is the ANSI syntax for doing an inner join.  You could replace the FROM and WHERE clause with:

FROM Table1 INNER JOIN Table2 ON Table1.pid = Table2.pid

and get the same result.

-Carl V.

[Dan_Bracuk]

Regarding:  "but the count comes from a table that only tracks the id."

I don't understand.  Your OP suggested that the count was being calculated in your query.

[Guest]

I just meant the count is calculated from the other table.  This worked.  Thank you.

[BKBK]

TheUrbanBrain wrote:

I just meant the count is calculated from the other table.  This worked.  Thank you.

Great! Please kindly mark this as answered. It will help someone else in future.

[Carl Von Stetten]

@TheUrbanBrain,

BKBK's version is close.  The problem is that any columns in your SELECT statement that aren't part of aggregate functions (like your COUNT function) must be named in the GROUP BY clause.  So a small to tweak to BKBK's code:

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

FROM Table1, Table2

WHERE Table1.pid = Table2.pid

GROUP BY Table2.name, Table1.pid

ORDER BY pcount DESC

should fix your issue.

-Carl V.