[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 548
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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