Geek Answers Handbook

Multiple Counters and Groups by

My table looks like this:

ID ProductName ProductCode 1 abc 123 2 abc 123 3 abc 456 4 def 789 5 ghi 246 6 jkl 369 7 jkl 369 8 jkl 369 9 jkl 468 10 jkl 468

And I want to create a pivot table that looks like this:

 ProductName ProductCode Total abc 123 2 abc 456 1 jkl 369 3 jkl 468 2

In other words, I am not interested in the products "def" and "ghi" because they appear only once in the source table. For everything else, I want to make a group by ProductName and ProductCode and display the numbers.

I tried playing with group by clauses and where in (select ...), but I just ended circles in circles.

The table has about 50,000 rows and is located on SQL Server 2008 R2.

+4
source share
4 answers

It is he:

 SELECT ProductName, ProductCode, COUNT(*) as Total FROM Table1 WHERE ProductName IN (SELECT ProductName FROM Table1 GROUP BY ProductName HAVING COUNT(*) > 1) GROUP BY ProductName, ProductCode

http://www.sqlfiddle.com/#!3/c79ad/9

+3
source

To filter an aggregate, you need to use the HAVING :

 SELECT ProductName, ProductCode, COUNT(*) as Total FROM T GROUP BY ProductName, ProductCode HAVING COUNT(*) > 1
0
source 
 ;WITH cte AS ( SELECT ID, ProductName, ProductCode, COUNT(*) OVER (PARTITION BY ProductName, ProductCode) AS Total, COUNT(*) OVER (PARTITION BY ProductName) AS PCount, ROW_NUMBER() OVER (PARTITION BY ProductName, ProductCode ORDER BY ID) AS rn FROM Products ) SELECT ID, ProductName, ProductCode, Total FROM cte WHERE PCount > 1 AND rn = 1
0
source

You cannot use WHERE in the column on which you are aggregating. Instead, you need to use HAVING .

 SELECT ProductName, ProductCode, COUNT(*) AS Total FROM Products p GROUP BY p.ProductName, p.ProductCode HAVING COUNT(p.ProductName) > 1
-1
source

More articles:


All Articles