Count and report instances of words

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Count and report instances of words

Author

Topic

K-Rad
Starting Member

14 Posts

Posted - 2012-07-17 : 22:14:21

Hi guys, this is a follow up question to one I asked earlier.

Basically, I have a field which is a string and any word(s) can be written in it. What I want to make this query do is spit out a list of all the different words that were put into this field and a count of how many times it was put in.

For example, if this data was used:


Word|

Monkey
Chicken
Chicken
Cockatiel
Spaniel
Monkey
Monky
Frog

Then I would want it to look like:

Word       |     Count      |
Monkey            2
Chicken           2
Cockatiel         1
Spaniel           1
Monky             1
Frog              1

Any suggestions? Pivot tables were recommended to me but upon more research I found that these are for use with numbers, not text so I am a bit stuck!

Any help would be greatly appreciated.

What a tragic waste of potential.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-07-17 : 22:20:54

[code]
declare @string varchar(100)

select @string = 'Monkey Chicken Chicken Cockatiel Spaniel Monkey Monky Frog'

select data as [Word], count(*) as [Count]
from dbo.sfn_parselist(' ', @string)
where data <> ''
group by data
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-17 : 22:57:38

is that space or carriage return you've as delimiter inside word?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-07-18 : 00:04:07

I'd like to point out that you should probably not be doing this in the DB. Seems more like a front end thing. C# & LINQ would have this licked in no time.
If you're intending to store the data like that then you should think again.

K-Rad
Starting Member

14 Posts

Posted - 2012-07-18 : 02:56:11

You're spot on LoztInSpace, I should have mentioned that in the op. I am just trying to write a query which will take the information stored in the table and sort it out for me, I don't want to store the data like this.

Khtan, I may be misreading how your code works, but the idea is that any amount or type of text can be entered so I wouldn't know what they are in advance to ' select @string = '.

To give a less abstract example and to explain exactly what I am trying to do (sorry, probably should have done this before), I have a person's name, class, and a 8 fields they can enter any information they'd like, specifically types of groups they belong to. For example, it will be input into the table similar to this:


Name     |    Class    |    G1    |     G2    |    G3    |    G4    |    G5    |    G6    |    G7    |    G8   
Bob            7A         Band         Choir      Tennis
Jim            7B         Tennis       Choir      Chess      Leader
Danny          7A         Choir        Soccer     Leader
Tony           7C         Tennis       Band

And I want the query to find it and sort it so it gives me the class, different groups entered (as they can change from time to time and can't be predefined in the DB) and a count, like this:

7A 
|    Group    |    Count    |
    Band           1
    Choir          2
    Tennis         1
    Soccer         1
    Leader         1

7B
|    Group    |    Count    |
    Tennis         1
    Choir          1
    Chess          1
    Leader         1

7C
|    Group    |    Count    |
    Tennis         1
    Band           1

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-07-18 : 03:15:40

[code]
select Class, Group, count(*)
from
(
select Class, Group = G1 from yourtable union all
select Class, Group = G2 from yourtable union all
select Class, Group = G3 from yourtable union all
...
) d
group by Class, Group
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-18 : 09:45:10

or use unpivot


select Class,[Group],count(*)
from yourtable
unpivot (val for [group] in ([G1],[G2],[G3],...))u
group by Class,[Group]

if groups cant be determined before hand use dynamic sql with above query. Prepopulate a variable with group list and pass it to UNPIVOT

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

K-Rad
Starting Member

14 Posts

Posted - 2012-07-19 : 22:32:11

Thanks for your help guys, unfortunately I am a bit dense :)

I am trying to use the 'union all' syntax and marry it with the code I already have. Currently I have a group of set fields which take integers and the query counts them and gives a total. That's simple enough, but the part I have been asking you about it meant to supplement that so in the end I have a list of the static numbers, totalled, and a list of the dynamic strings, totalled.

If anyone could take a peek at this code and tell me where I am going wrong it would be greatly appreciated. Hopefully I am not a million miles off track and you can see what I am trying to do:


SELECT 
	"YearClass" AS "Year & Class", 
	SUM( COALESCE( "ClassGrp", 0 ) ) AS "Laminated Class Photo", 
	SUM( COALESCE( "VP1", 0 ) + COALESCE( "VP2", 0 ) + COALESCE( "VP3", 0 ) + COALESCE( "VP7", 0 ) + COALESCE( "VP8", 0 ) + COALESCE( "VP9", 0 ) + COALESCE( "ClassGrp", 0 ) ) AS "Group Total" ,
	Count(*)

FROM "StudentInfo" 
	(select YearClass, Other_Group = GrpOther1 from "StudentInfo" union all
	select YearClass, Other_Group = GrpOther2 from "StudentInfo" union all
	select YearClass, Other_Group = GrpOther3 from "StudentInfo" union all
	select YearClass, Other_Group = GrpOther4 from "StudentInfo" union all
	select YearClass, Other_Group = GrpOther5 from "StudentInfo" union all
	select YearClass, Other_Group = GrpOther6 from "StudentInfo" union all
	select YearClass, Other_Group = GrpOther7 from "StudentInfo" union all
	select YearClass, Other_Group = GrpOther8 from "StudentInfo" union all)
	
GROUP BY "YearClass" (HAVING SUM( COALESCE( "VP1", 0 ) + COALESCE( "VP2", 0 ) + COALESCE( "VP3", 0 ) + COALESCE( "VP7", 0 ) + COALESCE( "VP8", 0 ) + COALESCE( "VP9", 0 ) + COALESCE( "ClassGrp", 0 ) ) > 0), 	"Other_Group"

ORDER BY "YearClass" ASC

The final result should look something like:


Year & Class | Laminated Class Photo | Group Total | Other_Group |

Or, if it works out easier to change the formatting slightly:


Year & Class:
Laminated Class Photo| Group Total | Other_Group |

Sorry I am so useless at this, I am trying to help out a friend and the both of us are pretty clueless, I just got defaulted with this part because I did some programming back in high school.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-19 : 22:48:13

it should be


SELECT t.YearClass AS "Year & Class",
[Laminated Class Photo],
[Group Total],
[OtherTotal]
FROM
(
SELECT 
	YearClass, 
	SUM( COALESCE( ClassGrp, 0 ) ) AS "Laminated Class Photo", 
	SUM( COALESCE( VP1, 0 ) + COALESCE( VP2, 0 ) + COALESCE( VP3, 0 ) + COALESCE( VP7, 0 ) + COALESCE( VP8, 0 ) + COALESCE( VP9, 0 ) + COALESCE( ClassGrp", 0 ) ) AS "Group Total" 
FROM StudentInfo 	
GROUP BY YearClass 
HAVING SUM( COALESCE( VP1, 0 ) + COALESCE( VP2, 0 ) + COALESCE( VP3, 0 ) + COALESCE( VP7, 0 ) + COALESCE( VP8, 0 ) + COALESCE( VP9, 0 ) + COALESCE( ClassGrp, 0 ) ) > 0
)t
INNER JOIN (select YearClass,
                   SUM(GrpValue) AS [OtherGroup]
            FROM StudentInfo s
            UNPIVOT (GrpValue FOR GrpCat IN (GrpOther1,GrpOther2,GrpOther3,GrpOther4,GrpOther5,GrpOther6,GrpOther7,GrpOther8))u
            GROUP BY YearClass
           )u
ON u.YearClass = t.YearClass
ORDER BY t.YearClass ASC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

K-Rad
Starting Member

14 Posts

Posted - 2012-07-20 : 02:50:14

Thanks a lot Visakh!

So instead of doing all of the work in the original 'SELECT' area, you use [] to declare that the variable value will be found in a following sub-function (SELECT), is that right?

Could you please confirm for me what the 'u' and 't' represents? I figure it is meant to tell me to put in whatever value I have specific for my database but I'm afraid I am not sure which you mean!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-20 : 10:16:46

yep..exactly. i've done it via derived table ie the inner select which gives you grouped total and acts itself as a table. u and t are aliases given for tables (short names). this will give you an easy way to refer the tables/subqueries again in query on joins etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

K-Rad
Starting Member

14 Posts

Posted - 2012-07-22 : 21:00:39

Sorry to keep being a pain Visakh but I've tried applying this code and I am getting some syntax errors. I've really got no idea what they are as I've tried playing around, putting " around table names, etc and so forth.

I am not too sure how the 's' in


FROM StudentInfo s

fits into things either? Sorry to keep wasting your time!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-07-22 : 22:47:51

can you post your query here ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-23 : 00:27:19

quote:
Originally posted by K-Rad

Sorry to keep being a pain Visakh but I've tried applying this code and I am getting some syntax errors. I've really got no idea what they are as I've tried playing around, putting " around table names, etc and so forth.

I am not too sure how the 's' in

FROM StudentInfo s

fits into things either? Sorry to keep wasting your time!

where's s? I dont have alias s in my query. please post query if its different from what i gave

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

K-Rad
Starting Member

14 Posts

Posted - 2012-07-23 : 00:42:23

quote:

Originally posted by visakh16

it should be


SELECT t.YearClass AS "Year & Class",
[Laminated Class Photo],
[Group Total],
[OtherTotal]
FROM
(
SELECT 
	YearClass, 
	SUM( COALESCE( ClassGrp, 0 ) ) AS "Laminated Class Photo", 
	SUM( COALESCE( VP1, 0 ) + COALESCE( VP2, 0 ) + COALESCE( VP3, 0 ) + COALESCE( VP7, 0 ) + COALESCE( VP8, 0 ) + COALESCE( VP9, 0 ) + COALESCE( ClassGrp", 0 ) ) AS "Group Total" 
FROM StudentInfo 	
GROUP BY YearClass 
HAVING SUM( COALESCE( VP1, 0 ) + COALESCE( VP2, 0 ) + COALESCE( VP3, 0 ) + COALESCE( VP7, 0 ) + COALESCE( VP8, 0 ) + COALESCE( VP9, 0 ) + COALESCE( ClassGrp, 0 ) ) > 0
)t
INNER JOIN (select YearClass,
                   SUM(GrpValue) AS [OtherGroup]
            FROM StudentInfo s
            UNPIVOT (GrpValue FOR GrpCat IN (GrpOther1,GrpOther2,GrpOther3,GrpOther4,GrpOther5,GrpOther6,GrpOther7,GrpOther8))u
            GROUP BY YearClass
           )u
ON u.YearClass = t.YearClass
ORDER BY t.YearClass ASC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I am using that code exactly as you wrote it as the table names, etc, are correct. To be honest I still don't quite understand how the aliases work/are referenced though. I'll do some more googling.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-23 : 00:57:04

it had an unwanted " character


SELECT t.YearClass AS [Year & Class],
[Laminated Class Photo],
[Group Total],
[OtherTotal]
FROM
(
SELECT 
	YearClass, 
	SUM( COALESCE( ClassGrp, 0 ) ) AS [Laminated Class Photo], 
	SUM( COALESCE( VP1, 0 ) + COALESCE( VP2, 0 ) + COALESCE( VP3, 0 ) + COALESCE( VP7, 0 ) + COALESCE( VP8, 0 ) + COALESCE( VP9, 0 ) + COALESCE( ClassGrp, 0 ) ) AS [Group Total] 
FROM StudentInfo 	
GROUP BY YearClass 
HAVING SUM( COALESCE( VP1, 0 ) + COALESCE( VP2, 0 ) + COALESCE( VP3, 0 ) + COALESCE( VP7, 0 ) + COALESCE( VP8, 0 ) + COALESCE( VP9, 0 ) + COALESCE( ClassGrp, 0 ) ) > 0
)t
INNER JOIN (select YearClass,
                   SUM(GrpValue) AS [OtherGroup]
            FROM StudentInfo s
            UNPIVOT (GrpValue FOR GrpCat IN (GrpOther1,GrpOther2,GrpOther3,GrpOther4,GrpOther5,GrpOther6,GrpOther7,GrpOther8))u
            GROUP BY YearClass
           )u
ON u.YearClass = t.YearClass
ORDER BY t.YearClass ASC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

K-Rad
Starting Member

14 Posts

Posted - 2012-07-23 : 03:01:34

Thanks for the persistence Visakh, although I am still getting syntax errors when I try to use that specific code.

I still am not sure where the 's' of FROM StudentInfo s comes from as I don't see 's' referenced anywhere else?

Also, I see you make the 'GrpValue' value when you UNPIVOT, but I don't see where 'GrpCat' is used?

Sorry for being so annoying, it's all taking much longer to sink in than it should!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-23 : 10:18:24

GrpCat is just returning headers so thats not required.

first run this and see if it works


select YearClass,
                   SUM(GrpValue) AS [OtherGroup]
            FROM StudentInfo s
            UNPIVOT (GrpValue FOR GrpCat IN ([GrpOther1],[GrpOther2],[GrpOther3],[GrpOther4],[GrpOther5],[GrpOther6],[GrpOther7],[GrpOther8]))u
            GROUP BY YearClass

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

K-Rad
Starting Member

14 Posts

Posted - 2012-07-23 : 19:26:04

Hm, unfortunately not. It gives me the same errors as the previous codes did so is it possible to be an issue with the version I am using or some such? I'll feel like a real idiot for wasting your time if so! I am trying to do all of this in OpenOffice Base with their SQL view.

The errors are:

1st


SQL Status: HY000
Error code: 1000

Syntax error in SQL expression

2nd:


SQL Status: HY000
Error code: 1000

3rd:


SQL Status: HY000
Error code: 1000

syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-23 : 21:03:49

where are you using this? i dont think so its not a t-sql error message

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

K-Rad
Starting Member

14 Posts

Posted - 2012-07-24 : 00:05:27

I am using in the latest version of OpenOffice Base but I am starting to think it may not support some of the more diverse SQL commands?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources