CODEWITHSUNDEEP
sql-servergroup-byhaving-clause
asif308
asif308

Reputation: 90

How to use SQL query with havng count here?

I having Table(Tbl_Test) with following data. 

         RecordId     BatchName         Numbers        MQC          
          1          20150443          321106          0    
          2          20150430          321107          0
          3          20150430          321107          1
          4          20150412          321110          2
          5          20150430          321118   
          6          20150430          321120   
          7          20150432          321120   
          8          20150430          321126   
          9          20150432          321127   
          10         20150430          321129
          11         20150431          321129

From the above table I want output like,Numbers columns whose count is greater than 1.

     RecordId     BatchName         Numbers        MQC          
      2          20150430          321107          0    
      3          20150430          321107          1
      6          20150430          321120   
      7          20150432          321120   
      10         20150430          321129
      11         20150431          321129

I've tried the following select but without success:

select RecordId,BatchName,Numbers,MQC 
from Tbl_Test 
group by RecordId,BatchName,Numbers,MQC 
having count(Numbers)>1

Upvotes: 1

Views: 86

Answers (4)

ughai
ughai

Reputation: 9890

You can use GROUP BY with HAVING and IN.Something like this.

SELECT * FROM Tbl_Test 
WHERE Numbers IN
(
    SELECT Numbers
    FROM Tbl_Test 
    GROUP BY Numbers
    HAVING COUNT(*) > 1
)

Upvotes: 3

t-clausen.dk
t-clausen.dk

Reputation: 44326

;WITH CTE AS
(
  SELECT 
    RecordId, 
    BatchName, 
    Numbers,
    MQC,
    count(*) over (partition by Numbers) cnt
  FROM tbl_test
)
SELECT 
  RecordId, BatchName, Numbers, MQC
FROM CTE
WHERE cnt > 1

Upvotes: 3

Zohar Peled
Zohar Peled

Reputation: 82474

One solution would be to use a Common table expression.
Try this:

;With CTE AS
(
    SELECT Numbers,
           Count(1) As NumberOfRows
    FROM Tbl_Test 
    GROUP BY Numbers
)

SELECT T.*
FROM Tbl_Test T
INNER JOIN CTE ON(T.Numbers = CTE.Numbers)
WHERE NumberOfRows > 1

play with it your self on sql fiddle

Upvotes: 2

Sagar
Sagar

Reputation: 647

Use count on RecordID with group By on MQC.

'Count(RecordId) Group By MQC' should work for you.

Upvotes: -1

Related Questions