Query to See Count of ProductName against same ProductId

Question

How to write Sql Query to show Number of Names based on one Product Id. For example in this example: For ProductId - 263, Count(Name) = 3

I would like to see

263 3
264 2 
265 10
266 0 (if null)

`select productid,count(name) from tablename group by productid` and how you know the next id like 266 — wiretext, Oct 08 '15 at 16:05

score 3 · Answer 1 · edited Dec 26 '15 at 05:12

3

SELECT productid, COUNT(*)
FROM products 
GROUP BY productid

It's not an exact answer, but it will return the number of occurrences of the productid for each unique productid. It may help you find your result.

edited Dec 26 '15 at 05:12

Pang

9,564
146
81
122

answered Oct 09 '15 at 12:40

Gordon Linoff · Answer 2 · 2015-10-08T20:57:50.120

1

Assuming you have a table of products, then you want a left join:

select p.productid, count(pn.productid)
from products p left join
     productnames pn
     on p.productid = pn.productid
group by p.productid;

edited Oct 08 '15 at 20:57

answered Oct 08 '15 at 16:07

Gordon Linoff

1,242,037
58
646
786

Hi @gordonlinoff would you, please, take a time to look at this [answer](http://stackoverflow.com/a/33006952/460557) it is not related to this question but I think it worth to a specialist as yourself to take a look. The problem involve this two links [sqlfiddle 1](http://sqlfiddle.com/#!2/c77d8/7) and [sqlfiddle 2](http://sqlfiddle.com/#!9/c77d8/182) notice the last value on the `quantity_diff` column – Jorge Campos Oct 08 '15 at 16:43

score 0 · Answer 3 · answered Nov 17 '16 at 17:26

Pang.

I have written an answer using a CTE that will give you the count of all the names for each product that can easily be changed to COUNT the DISTINCT names.

   ;WITH    CTE_DISTINCT_NAMES
        AS (SELECT      --DISTINCT 
                        /* Uncomment "DISTINCT" above 
                           to include duplicate names 
                           in the COUNT
                           */
                        ProductId   
            ,           Name        
            FROM        TABLE1
            )
        SELECT      T.ProductId
        ,           COUNT(ISNULL(T.Name,'')) AS [COUNT(Name)]
        FROM        CTE_DISTINCT_NAMES AS T
        GROUP BY    T.ProductId

score -2 · Answer 4 · edited Oct 09 '15 at 12:38

-2

Try this solution:

SELECT 
    PRODUCT_ID AS PID,
    COALESCE(COUNT(NAME), 0) AS CNT
FROM YOUR_TABLE
GROUP BY PRODUCT_ID

edited Oct 09 '15 at 12:38

veljasije

6,722
12
48
79

answered Oct 09 '15 at 11:58

BalajiShriram

1
2

SELECT PRODUCT_ID AS PID,ISNULL(COUNT(NAME),0) AS CNT FROM YOUR_TABLE GROUP BY PRODUCT_ID – BalajiShriram Oct 09 '15 at 11:59

Query to See Count of ProductName against same ProductId

4 Answers4