select count column?

Question

1.00/5 (1 vote)

See more:

I'm working with sql server. I have 3 tables. The 2nd has a reference to the 1st and the 3rd a reference to the 2nd (both relationships 1 to many). I want to have a joining table that includes a column showing how many times each record (or row) in the 2nd table appears in 3rd table. The 1st column represent PK, 2nd column FK :

SQL

Category    Product    Orders
--------    -------    ------
1           1  3       1  4
2           2  1       2  4
3           3  1       3  3
            4  3       4  2
                       5  2
                       6  4
                       7  2

My joining table might look something like this:

Product     Price      Category     Number of Orders
--------    -------    --------     ----------------

Posted 12-May-15 19:20pm

Member 10466855

Updated 13-May-15 9:13am

v2

Add a Solution

Comments

King Fisher 13-May-15 1:29am

What you have tried so far?

3 solutions

Solution 1

by this way,

SQL

select count(*),a.uniqueId from table1 as a inner join table2 as b on a.uniqueId=b.foreignKeyId Group by a.uniqueId

Posted 12-May-15 19:31pm

King Fisher

Comments

King Fisher 13-May-15 1:55am

what?

Solution 2

I think you may have to be more specific, perhaps by providing a sample schema, as your requirements are (to me at least) unclear.

Typically, a join table is used for many-to-many relations, and each row of the join table only references each of the joined tables once - I'm obviously not understanding something here.

I suspect that you may be looking for computed columns[^]

Posted 12-May-15 19:40pm

pt1401

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 10466855 · Accepted Answer · 2015-05-13T10:28:00

Solution 3

Ok I figured it out:

select productName,price,category,(select count(*) from Orders where ProductID=1) as NumofOrders
		from Products
		inner join Category on Product.CategoryID=Category.CategoryID
		inner join Orders on Orders.ProductID=Product.ProductID
		where Product.ProductID
		group by productName,price,category

Posted 13-May-15 10:28am

Member 10466855

Updated 13-May-15 10:58am

v2

Comments

PSK_ 14-May-15 7:16am

Instead of "select count(*) from Orders where ProductID=1" try using CROSS APPLY for better performance.

select count column?

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

select count column?

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us