sql query to count two columns with same values

Question

I've got a table with 2 columns with keywords, and I need to count the occurrence of them. I can do that separately, one column at the time, and add the totals later, with a regular count,

select count (id), kw1 from mytable group by kw1

and the same for kw2, but I need to get the info straight from the db.

So the table is something like:

id       kw1          kw2
1         a            b
2         c            d 
3         b             
4         e            a

so the idea is to get how many times has been used each keyword, so the result should be something like:

'a'  2
'b'  2
'c'  1
'd'  1
'e'  1

Thanks in advance

PS: Sorry, I forgot, but just in case, I'm working on Oracle 10g

Mahmoud Gamal · Accepted Answer · 2012-12-03 11:59:30Z

3

Try this:

SELECT kw, COUNT(kw)
FROM
(
   SELECT "kw1" AS kw FROM table1
   UNION ALL 
   SELECT "kw2"       FROM table1
) t
WHERE kw IS NOT NULL
GROUP BY kw
ORDER BY KW;

SQL Fiddle Demo

This will give you:

KW   COUNT(KW)
a       2
b       2
c       1
d       1
e       1

edited Dec 3, 2012 at 11:59

answered Dec 3, 2012 at 11:52

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Seba Over a year ago

Thanks, It worked, but I have some differences, I think it has to do with other fields, from other tables that I need to filter.... It's done.Thanks again!!

Augusto · Accepted Answer · 2012-12-03 12:34:55Z

2

It shoud looks something like this.

SELECT kw,SUM(kw)
FROM(
(SELECT kw1 AS kw, COUNT(kw1) 
FROM table
WHERE kw1 IS NOT NULL GROUP BY kw1) skw1

UNION ALL

(SELECT kw2, COUNT(kw2) 
FROM table
WHERE kw2 IS NOT NULL GROUP BY kw2) skw2
)
GROUP BY kw
ORDER BY kw

Previous answers don't perform a SUM operation after performing the UNION.

edited Dec 3, 2012 at 12:34

answered Dec 3, 2012 at 12:27

Augusto

8176 silver badges18 bronze badges

2 Comments

Frank Schmitt Over a year ago

This query won't work because you get a syntax error for the table aliases skw1 / skw2 (not allowed there) and because you cannot SUM strings (either you have to use COUNT, or you should alias your COUNT columns from the inner query and SUM this column instead).

Augusto Over a year ago

@FrankSchmitt you're totally right. As matter of fact, the sum should be performed on the second column of the subqueries. There's also a mistake about aliasing. I'm at work so I wasn't able to try it at the momento of posting the reply. Thanks for your comment!

Ravindra Bagale · Accepted Answer · 2012-12-03 12:33:20Z

0

use union All to combine two column data into one column & then count occurence

 SELECT cnt, COUNT(cnt)
    FROM
    (
       SELECT kw1 AS cnt FROM table
       UNION ALL 
       SELECT kw2       FROM table
    ) t
 WHERE cnt IS NOT NULL
GROUP BY cnt
ORDER BY cnt;

edited Dec 3, 2012 at 12:33

answered Dec 3, 2012 at 11:54

Ravindra Bagale

17.7k9 gold badges47 silver badges70 bronze badges

Collectives™ on Stack Overflow

sql query to count two columns with same values

3 Answers 3

SQL Fiddle Demo

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related