Group By statement as a column

I have a pseudo SQL statement like

select [orderid], [productname], [price], [productid_FK]
from Order

This will give me 5 rows:

So the result is 5 rows of which 3 have a [productid_FK] of "2". This FK-column references to the table called [Product].

Now lets say I want an extra column based on the [productid_FK].

The column 开发者_开发百科must contain the total number of products in another table called [products]

Can I achieve this by enchaning the current pseudo query?

---

One way to get the results is a subquery. There are several ways to do this depending on the DB. I often do it this way

SELECT o.[orderid], 
       o.[productname], 
       o.[price], 
       [productid_FK], 
       p_counts.k 
FROM   order o 
       INNER JOIN (SELECT productid_fk, 
                          COUNT(product_id) k 
                   FROM   products 
                   GROUP  BY productid_fk) p_counts 
         ON o.productid_fk = p_counts.productid_fk 

---

SELECT p.productid, p.productname, p.price, COUNT(o.orderid)
FROM Order AS o
JOIN Product AS p ON o.productid_FK = p.productid
GROUP BY p.productid, p.productname, p.price

---

In MS SQL Server 2008, you can use the OVER clause on aggregations (count, sum, etc.). This will work (or at least, the version I wrote for my own tables worked:

SELECT [orderid], [productname], [price], [productid_FK]
 ,count(*) over (partition by [productid_FK])
 from Order 

It still weirds me out that this does what it does. Presumably, other RDBMSs have similar functionality.

---

SELECT o.orderid, o.productname, o.price, o.productid_FK, COUNT(o.productid_FK)
FROM order o JOIN products p on o.productid_FK = p.productid
GROUP BY o.productname

should do the trick for the start.

It seems like you're trying to find out, how many products belong to an order. Maybe you should consider a slightly different architecture design for that.

[Order] and [Product] are related n:m, which means: An order can consist of many products and a product can be part of many orders. So my suggestion would be to handle this relationship with a third table, e.g. [OrderProducts] having the fields [orderid] and [productid]. This way, your [Order] and [Product] tables would contain only unique orders and products like they're supposed to. Also, you are now having one dataset in [Order] for each one of the corresponding products, so you have a really big overhead from all the attributes of an order. And in the relationship-table, you could also store e.g. a special price for each product in each order if it deviates from the normal price or something like that.