• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 557
  • Last Modified:

union query count on word

I have the following which works but i want to count the number of words from each field.

SELECT word2, count([word2]) as countword
from qryword2
union
SELECT word3, count([word3]) as countword
from qryword3
union
SELECT word4, count([word4]) as countword
from qryword4
union
SELECT word5, count([word5]) as countword
from qryword5
union
SELECT word6, count([word6]) as countword
from qryword6
union
SELECT word7, count([word7]) as countword 
from qryword7
UNION
SELECT word8, count([word8]) as countword
from qryword8;

Open in new window

0
PeterBaileyUk
Asked:
PeterBaileyUk
  • 4
  • 2
1 Solution
 
PeterBaileyUkAuthor Commented:
the counting doesnt work
0
 
Rey Obrero (Capricorn1)Commented:
SELECT word2 as [Word], count([word2]) as countword
from qryword2
group by word2
union all
SELECT word3 as [Word], count([word3]) as countword
from qryword3
group by word3
union all

etc...
0
 
PeterBaileyUkAuthor Commented:
yep worked but i needed sum and to get rid of nulls how do i stop nulls?
SELECT word2, sum([word2]) as countword
from qryword2
where word2 is not null
group by word2
union
SELECT word3, sum([word3]) as countword
from qryword3
where word3 is not null
group by word3
union
SELECT word4, sum([word4]) as countword
from qryword4
where word4 is not null
group by word4; ....
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Rey Obrero (Capricorn1)Commented:
yes that is correct,
or you could have done it in the other queries "qryword?"
0
 
PeterBaileyUkAuthor Commented:
Yes i did that so no nulls are returned but it says datatype mismatch when i try to use sum?

here is an example output of the sub :
Word2      CountOfWord2
BUSINESS             4
R-DESIGN            17
SE                    15
eeex.PNG
0
 
PeterBaileyUkAuthor Commented:
got it thank you:

SELECT word2, sum([countofword2]) as countword2
from qryword2
group by word2
union
SELECT word3, sum([countofword3]) as countword3
from qryword3
group by word3
union
SELECT word4, sum([countofword4]) as countword4
from qryword4
group by word4
union
SELECT word5, sum([countofword5]) as countword5
from qryword5
group by word5
union
SELECT word6, sum([countofword6]) as countword6
from qryword6
group by word6
union
SELECT word7, sum([countofword7]) as countword7
from qryword7
group by word7
union
SELECT word8, sum([countofword8]) as countword8
from qryword8
group by word8;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now