QUERY counting 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

QUERY counting instances of words

Author

Topic

K-Rad
Starting Member

14 Posts

Posted - 2012-07-05 : 02:22:05

Hi guys, I'm not 100% sure this is the place to ask this question but I couldn't see anywhere else more obviously relevant so here goes!

My problem is this: I have a query which needs to find a bunch of totals, grouping together some things to find a sub-total, and to find a grand total. Mostly these are simple numerical values that come in under one field each. The spanner gets thrown into the works when I have a final option available for what is essentially a catchall for all other potential options.

The people using the database can enter in a numerical value for products: VP1, VP2, VP3, VP7, VP8, VP9, ClassGrp however they can also enter in a string for 8 fields, GrpOther1-8. For example, GrpOther1 could be 'choir', GrpOther2 could be 'band', etc. There could be nothing entered in here also.

What I used to do with a spreadsheet was sort them into a single column and run a COUNTIF. I am unsure how to make this work with the current code:



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"

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

ORDER BY "YearClass" ASC

Ideally at the end I'll have the 'group total', 'laminated class photo' and then other headings with totals depending on what is input into the field.

Any help would be hugely appreciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-05 : 09:48:12

sorry not quite clear from your explanation. can you post some sample data to illustrate the scenario. that should help us better to understand and help you out!

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

K-Rad
Starting Member

14 Posts

Posted - 2012-07-05 : 21:13:21

No worries Visakh, sorry!

Currently people can enter something like this:


ClassYear | VP1 | VP2 | VP3 | VP4| VP5 | VP6 | VP7 | VP8 | VP9 | ClassGrp| Port1 | Port2 | Port3 | Fam1 | Fam2 | Fam3 | GrpOther1 | GrpOther2 | GrpOther3 | GrpOther4(etc to 8) |
   12A       1     0     0     0    0     0     1     0     0        1       0       0       1        0    1       0      Choir       Band          0           0
   12A       0     1     0     0    1     0     0     0     0        0       1       0       0        1    0       1      Band        Chess       Karate        0
   12B       0     1     0     0    0     1     0     0     1        1       0       0       1        0    1       0      Band        Leader      Karate        0
   12B       0     0     1     1    0     0     0     1     0        1       0       1       0        1    0       0      Choir       Leader      Karate        0

The current query is only interested in counting VP1-3, VP7-9, ClassGrp and GrpOther1-8 and totalling by ClassYear, so the final result should look something like:

ClassYear | Laminated Class Photo | Group Total | Band | Choir | Chess | Karate | Leader
  12A              1                    3           2      1       1       1       0
  12B              2                    6           1      1       0       2       2

I hope this makes more sense! Thanks again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-05 : 22:50:57

GROUP BY ClassYear and then for band,Choir etc add expression like

SUM(CASE WHEN GrpOther1='Choir' THEN 1 ELSE 0 END + CASE WHEN GrpOther2='Choir' THEN 1 ELSE 0 END + CASE WHEN GrpOther3='Choir' THEN 1 ELSE 0 END +... + CASE WHEN GrpOther8='Choir' THEN 1 ELSE 0 END) AS Choir,...

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

K-Rad
Starting Member

14 Posts

Posted - 2012-07-06 : 01:16:56

Thanks Visakh, that's the obvious answer but the problem is that there can be anything up to 40 possible inputs for that range and they are changing all of the time as different groups are created and moved. Is it possible to work with what is input instead of assigning a range in advance?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-06 : 09:27:50

in that case only option is to dynamically do an UNPIVOT and then take sum based on groups available

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

K-Rad
Starting Member

14 Posts

Posted - 2012-07-08 : 23:41:18

Thanks a lot Visakh! I am looking into how to use pivot/unpivot now, at first glance it appears to be able to solve my problem :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-09 : 00:18:40

welcome
let me know if you face any issue

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources