count with where clause multiple condition on same table

Question

I have a table called products.

ProductId| ProductName| ProductType| ProductSize
1        | a          | yellow     |  12
2        | b          | green      |  13
3        | c          | yellow     |  12
4        | d          | yellow     |  15
________________________________________________

I want to get count of each product as a column at end, where productType, and ProductSize match, the excepted result I want to be..

ProductID|ProductName|ProductType|ProductSize|TotalProduct
1        | a         | yellow    | 12        | 2
2        | b         | green     | 13        | 1
3        | c         | yellow    | 12        | 2
4        | d         | yellow    | 15        | 1
_________________________________________________________

some what I have try, but failed is look like this.

select ProductId, ProductName, ProductType, ProductSize,
(select count(*) from Product where ProductType=(Products.ProductType) and ProductSize=(products.productSize)) as [TotalProduct] from Products

its return totalProduct = 4 for all the record. thanks

try this http://stackoverflow.com/questions/9664043/how-can-i-count-distinct-multiple-fields-without-repeating-the-query — sourabh kasliwal, Jun 11 '13 at 14:11
Please specify the RDBMS that you are targeting by adding the appropriate tag (Oracle, SQL Server, MySQL, etc.). There may be answers that take advantage of language or product features that are not universally supported. Also, by tagging it with a specific RDBMS, your question may receive attention from people better suited to answer it. — Taryn, Jun 11 '13 at 14:23

score 2 · Accepted Answer · answered Jun 11 '13 at 14:17

In most versions of SQL, you would use window/analytic functions:

select ProductId, ProductName, ProductType, ProductSize,
       count(*) over (partition by producttype, productsize)
from products p

The problem with your query is that you are not giving the table names aliases. So an expression like ProductSize=(products.productSize) is not correlated with the outer query. It is actually equivalent to ProductSize = ProductSize on the inner query. You could fix that by just putting from Products p in the inner query. However, the window function approach is better in databases that support it (most of them).

score 0 · Answer 2 · answered Jun 11 '13 at 14:27

You can get the result by using a subquery to get the count for each producttype and productsize that match:

select producttype, productsize, count(*) TotalProduct
from product
group by producttype, productsize;

See SQL Fiddle with Demo.

Then you can join your product table to this subquery to get the final result:

select p1.productid,
  p1.productname,
  p1.producttype,
  p1.productsize,
  p2.totalProduct
from product p1
inner join
(
  select producttype, productsize, count(*) TotalProduct
  from product
  group by producttype, productsize
) p2
  on p1.producttype = p2.producttype
  and p1.productsize = p2.productsize;

See SQL Fiddle with Demo. This gives a result:

| PRODUCTID | PRODUCTNAME | PRODUCTTYPE | PRODUCTSIZE | TOTALPRODUCT |
----------------------------------------------------------------------
|         1 |           a |      yellow |          12 |            2 |
|         2 |           b |       green |          13 |            1 |
|         3 |           c |      yellow |          12 |            2 |
|         4 |           d |      yellow |          15 |            1

count with where clause multiple condition on same table

some what I have try, but failed is look like this.

2 Answers2