CODEWITHSUNDEEP
mysqlsqlsqlite
Hardik
Hardik

Reputation: 259

count same value from different columns in same table

I have table which contains some columns, where two columns can contains same value. Mine requirement is if one of them column contains value more then 6 times or both can combine having greater then 6 times so it will be output. 

REF     NONREF
--------------
7112    6162

7112    6162

7112    6162

7112    6162

7112    6162

7112    6162

6162    6237

7112    6237

Output should be ::
-------------------
7112

6162

As like above, 

          REF having '7172' (7 times)
          NONREF having '6162' (6 times) + REF having '6162' (1 time) = (7 times)

I tried below query but I know it's not as per requirement

Select REF, count(*) from RDF_LINK
GROUP BY REF
Having count(*) > 6
UNION
Select NON, count(*) from RDF_LINK
GROUP BY NONREF
Having count(*) > 6

Please Help

Upvotes: 1

Views: 1122

Answers (2)

Jithin Shaji
Jithin Shaji

Reputation: 6073

this couls also help you..

DECLARE @TAB TABLE (REF INT, NONREF INT)
INSERT INTO @TAB 
VALUES  (7112,6162),(7112,6162),(7112,6162),
        (7112,6162),(7112,6162),(7112,6162),
        (6162,6237),(7112,6237)
--SELECT * FROM @TAB

SELECT REF,
        COUNT(*) [COUNT] 
FROM    @TAB GROUP BY REF HAVING COUNT(*) > 6
UNION ALL
SELECT  NR.NONREF,
        NR.COUNT + R.COUNT [COUNT]
FROM    (SELECT NONREF,COUNT(*) [COUNT] FROM @TAB GROUP BY NONREF) NR
JOIN    (SELECT REF,COUNT(*) [COUNT] FROM @TAB GROUP BY REF) R ON NR.NONREF = R.REF
WHERE   (NR.COUNT + R.COUNT) > 6

Result.

enter image description here

Upvotes: 1

CL.
CL.

Reputation: 180080

First combine the two columns into one list:

SELECT Ref AS Value FROM RDF_Link
UNION ALL
SELECT Non          FROM RDF_Link

The use that as the source table for the grouping:

SELECT Value, COUNT(*)
FROM (SELECT Ref AS Value FROM RDF_Link
      UNION ALL
      SELECT Non          FROM RDF_Link)
GROUP BY Value
HAVING COUNT(*) > 6

Upvotes: 4

Related Questions