counting value in a field and outputing a table

Question

counting value in a field and outputing a table

k.furness

SSC Enthusiast

Points: 119
More actions
January 11, 2008 at 3:34 am

#70711

I have the following table of date
Q1 Q2 Q3
1 2 1
1 3 2
3 1 2
I need to be able to count each value and output a table showing the count for each number starting with 1's, 2's and 3's the 1's will be in row 1, 2's be in row 2 and 3's in row 3
Q1 Q2 Q3
2 1 1
0 1 2
1 1 0
How can I do this

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

k.furness (1/11/2008)
I have the following table of date
Q1 Q2 Q3
1 2 1
1 3 2
3 1 2
I need to be able to count each value and output a table showing the count for each number starting with 1's, 2's and 3's the 1's will be in row 1, 2's be in row 2 and 3's in row 3
Q1 Q2 Q3
2 1 1
0 1 2
1 1 0
How can I do this

You could use a tally table and three counts like:

SELECT TOP 100

IDENTITY( int, 1,1 ) nr

INTO tally

FROM sys.all_objects

SELECT nr

, COALESCE(tq1.CQ1, 0) AS Q1

, COALESCE(tq2.CQ2, 0) AS Q2

, COALESCE(tq3.CQ3, 0) AS Q3

FROM tally

LEFT JOIN ( SELECT COUNT(Q1) AS CQ1

, Q1

FROM mytable

GROUP BY Q1

) AS tq1 ON tally.nr = tq1.Q1

LEFT JOIN ( SELECT COUNT(Q2) AS CQ2

, Q2

FROM mytable

GROUP BY Q2

) AS tq2 ON tally.nr = tq2.Q2

LEFT JOIN ( SELECT COUNT(Q3) AS CQ3

, Q3

FROM mytable

GROUP BY Q3

) AS tq3 ON tally.nr = tq3.Q3

WHERE tq1.CQ1 IS NOT NULL

OR tq2.CQ2 IS NOT NULL

OR tq3.CQ3 IS NOT NULL

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

k.furness SSC Enthusiast Points: 119 More actions · Answer 2

Hi Andras

Thanks for the swift reply.

Not sure if this will work as I forgot to mention there are 150 fields Q1 to Q150

Tried to run script and got an error regarding "sys.all_objects"

Kind Regards

Kevin

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 3

k.furness (1/11/2008)
Hi Andras
Thanks for the swift reply.
Not sure if this will work as I forgot to mention there are 150 fields Q1 to Q150
Tried to run script and got an error regarding "sys.all_objects"
Kind Regards
Kevin

Hi Kevin,

Sorry, I assumed that you are running SQL Server 2005 (the forum is a 2005 forum :)) Most likely that is the reason for the missing sys.all_objects. You can replace it with a table that has plenty of rows and has a primary key. I use it only to build up a tally table.

So on 2000 you can use something like:

SELECT TOP 200

IDENTITY( int, 1,1 ) nr

INTO tally

FROM sysobjects

-- ... and the the second part of my query

Concerning the number of columns, obviously a case with 150 columns is very different from only 3 like in your example 🙂 The solution would be much more specialized, so a few questions: how many rows do you expect to have in your original table? Is there a maximum value for the number of occurances?

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

k.furness SSC Enthusiast Points: 119 More actions · Answer 4

Hi Andras

Thanks

On average I would expect around 6 rows for looking up values 0 to 5.

These values come from questions so depending on how many options are listed depends on the rows required.

I was also looking at using further rows to put min/max/avg values.

I understand this is not straight forward, hence the need for help.

I currently I am using the following method to get the data:-

Set dbtable = DBConn.execute("select count(Q1) AS QV1_0 from MYTABLE and Q1 = 0")

QV1_0 = dbtable.fields("QV1_0").value

Set dbtable = DBConn.execute("select count(Q1) AS QV1_1 from MYTABLE and Q1 = 1")

QV1_1 = dbtable.fields("QV1_1").value

Set dbtable = DBConn.execute("select count(Q1) AS QV1_2 from MYTABLE and Q1 = 2")

QV1_2 = dbtable.fields("QV1_2").value

Set dbtable = DBConn.execute("select count(Q1) AS QV1_3 from MYTABLE and Q1 = 3")

QV1_2 = dbtable.fields("QV1_2").value

Set dbtable = DBConn.execute("select count(Q2) AS QV2_0 from MYTABLE and Q2 = 0")

QV2_0 = dbtable.fields("QV2_0").value

Set dbtable = DBConn.execute("select count(Q2) AS QV2_1 from MYTABLE and Q2 = 1")

QV2_1 = dbtable.fields("QV2_1").value

Set dbtable = DBConn.execute("select count(Q21) AS QV2_2 from MYTABLE and Q2 = 2")

QV2_2 = dbtable.fields("QV2_2").value

Set dbtable = DBConn.execute("select count(Q2) AS QV2_3 from MYTABLE and Q2 = 3")

QV2_2 = dbtable.fields("QV2_2").value

On some reports they may be up to 300 executes to get all the data. So anything that will improve this would be great.

Kind Regards

Kevin

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 5

Step 1 should be to nomalize the table so things like this become much easier.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

k.furness SSC Enthusiast Points: 119 More actions · Answer 6

Hi Jeff

This is single table that contains the following Fields:

ID [int] Identity

QuestionnaireID [int]

Q0 [smallint]

.

Q150 [smallint]

Don't think I can get it any normalized than it is, but if I am wrong please explain

Kind Regards

Kevin

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 7

k.furness (1/12/2008)
Hi Jeff
This is single table that contains the following Fields:
ID [int] Identity
QuestionnaireID [int]
Q0 [smallint]
.
.
.
Q150 [smallint]
Don't think I can get it any normalized than it is, but if I am wrong please explain
Kind Regards
Kevin

Sure... the normalized form would not allow for possible nulls in columns if there were less than 150 questions...

ID [int] Identity

QuestionnaireID [int]

QuestionNumber [tinyint]

Response [smallint]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

k.furness SSC Enthusiast Points: 119 More actions · Answer 8

Hi Jeff

mmm, I see your point but are they any performance issues doing this.

I see we would be running the same amount of searches extracting the data.

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 9

Heck yeah there would be performance issues... performance would increase because you could properly index it 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

k.furness SSC Enthusiast Points: 119 More actions · Answer 10

So returning more rows with a 1 column is better than returning less rows with more columns.

What if I have 100 questionnaires containing 50 questions with 100,000 participants complete each questionnaire.

I would have a table with (100*50*100,000) 500,000,000 rows, wouldn't it be better to have the response as Q0 to Q50 only 10,000,000 rows.

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 11

Heh... what happened to 150 questions?

The real problem with the way you wanted to do it is that if most of your questionaires only have 50 questions and you have horizontally formatted rows that each contain 150 questions, you still have fewer rows per page... your 10,000,000 questions will actually take up more room page wise and most of that space will be wasted. Plus, it makes it much more difficult to tally the answers because you will need 150 case statements.

With the vertical format I proposed, you only need 5 case statements in a nice tight crosstab that will run very fast because of the indexing you'll be able to do.

Plus, with your horizontal fixed format of 150 questions, you're toast when they add just one more question.

Don't just take my word for it, though... create a million row test and see.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

k.furness SSC Enthusiast Points: 119 More actions · Answer 12

thanks for quick reply. on average we have about 50 questions to set 150 cols to support extra questions.

I see your way accommodates for any length of questionnaires. Not that clued up with SQL so not sure which is the way to go.

Need to look at performance issues when report. A friend of mine, suggest something like you solution previously.

Previously I created a table for each questionnaire, but I have too many know.

kind regards

kevin

k.furness SSC Enthusiast Points: 119 More actions · Answer 13

I accept your way is the correct way, however I have an issue with the way the data will be inputted into the table.

For every questionnaire completed means on average 50 input statements.

If the questionnaire is updated this would mean 50 updates statements

so either way I am going to have some big issues.

Is it best to have a single input/update or the 50?

In which case is it best to have the table normalised or my way with the 150 columns?