wildcard in SELECT

[ranger]

I have 96 fields named s1, s2, s3... s96 with hundreds of rows in a msSQL2000
db. I need to get the sum of the fields and was wondering if there is a way to
do a WILDCARD in the SELECT part of the statement as in: SELECT SUM(s1) +
SUM(s2) + SUM(s3)... AS ttl So would be: SELECT SUM(s%) AS ttl or something
------------------------- Also would like to have 's% <> 0' in WHERE
statement. tnx in advance

[vkunirs]

Hi

I think we can't do in that way.

try by writing a store procedure. there you may get it.

[Kronin555]

I've never seen wildcards in SQL.

This works in PostgreSQL:

SELECT SUM(s1 + s2 + s3 + ...) AS ttl
FROM table
WHERE s1 <> 0 AND s2 <> 0 and s3 <> 0 and ...

very tedious, but what you're asking for isn't possible (with the wildcard).

[jonwrob]

If you have a table with 96 columns that you want to add together, I suggest
your problem isn't with SQL, it's with your table design. What is it that
groups a row together? Would it not be better to have a table with two or
three columns, one with the value, and one with what groups a row together,
and one with what groups a column together.

Then your SQL would look like this

select sum (s)
from table
group by other_columns;

JR

[ranger]

jonwrob trying to second guess another developers work and then tell them
their wrong is kinda uncool methinks. table is for a survey site and 96 fields
are for 96 multiple choice questions with select posting from NULL to 5 for
each question.... thus s1, s2, s3..... client wants to distil in multiple
groupings as in s1, s13. s25.... also total table numbers. as i said in my
first posting can be hundreds and eventually 1000s of replys for each survey
(table) i have got the query to work by (SUM(s1) + SUM(s2)........). just
cumbersome to write though seems to work very fast. just seeing if 'shorthand'
for expressions is all.

[sdwebguy99]

Egos aside, I think jonwrob is right. A 96 column table is usually bad design
under most circumstances. Changing the table structure would allow you to have
n columns if ever your survey adds questions, deletes questions, etc.
Nonetheless, in a stored procedure you could loop over the columns in the table
using the metadata tables.. that way you wouldnt have to hardcode it. It would
allow you to build the SUM() + SUM() statement dynamically. Good luck with
that.

[ranger]

this isn't an ego issue... how would you record 96 multiple choice queries from
hundreds (or more) participants. using an comma delim list would mean cf would
be doing the heavy lifting for variations on which query numbers are polled at
any given time. sql2k thinks a table with 100 fields is puny.... why don't you
just answer if you ACTUALLY have a solution to the problem. i am doling
multiple SQL polling and full table SQL polling and taking under 300ms inc. cf
functions on report page. as i said before, just wondering if a shortcut, not
a lecture. tnx again

[jonwrob]

I'll take another shot at this. I don't know why, must be glutton for
punishment.

The problem is not that your table has 96 columns, the problem is that you
have 96 columns with the same data. Good database design principles suggest
this should be one column with multiple rows.

I would design your table with four columns as such:

ID (primary key column)
S (this is your actual answer, the columns you named s1...s96)
QUESTION (column that identifies which question the row is a response to,
values are probably 1...96)
REPLY (column that groups all the answers to one survey by a single person)

Then, your query looks like this:

select sum (s)
from table
where question in (1,2,25)

Other analysis can be done by changing the where clause.

See, your real problem is a badly designed table that resulted your having to
write a cumbersome query to perform the desired analysis. With a well designed
table, the query is simple and straight-forward.

Accept my help or not, I don't care.

JR

[paross1]

Sounds like someone (ranger) needs a few lessons in data modeling. This seems
to be a classic normalization (first normal form) issue with multi-valued
attributes.

[url]http://www.datamodel.org/DataModelReference.html[/url]

Phil

[ranger]

i dont even know where this is coming from but see no reason to debate with you
except to say that what you are saying would create a table with almost 30,000
rows for a survey with 300 respondants. a major read, write update load for
both SQL and CF severs. all i will respond to is does anyone know a simple way
to write: (SUM(s1) + SUM(s13) + SUM(s25) + SUM(s37) + SUM(s49) + SUM(s61) +
SUM(s73) + SUM(s85)) / (COUNT(s1) + COUNT(s13) + COUNT(s25) + COUNT(s37) +
COUNT(s49) + COUNT(s61) + COUNT(s73) + COUNT(s85)) 't1', 7 of these
statements also filtered in WHERE statement takes 150ms thas all folks.

[bradwood.com]

If you have access to your system tables, then a query like this:
SELECT c.name
FROM sysobjects o INNER JOIN
syscolumns c ON o.id = c.id
WHERE (o.name = 'table_name')

will get you a list of all the columns in your table. If you loop over that
result set with a cursor you could build a query string to be executed as a
prepared statement. Needless to say I personally think this would be much
more rigourus and slow than just typing the whole thing out. Good luck.

[Jochem van Dieten - TMM]

ranger wrote:

> i dont even know where this is coming from but see no reason to debate with you
> except to say that what you are saying would create a table with almost 30,000
> rows for a survey with 300 respondants. a major read, write update load for
> both SQL and CF severs.

Databases uaually scale better in the number of rows then in the
number of columns.

> all i will respond to is does anyone know a simple way
> to write: (SUM(s1) + SUM(s13) + SUM(s25) + SUM(s37) + SUM(s49) + SUM(s61) +
> SUM(s73) + SUM(s85)) / (COUNT(s1) + COUNT(s13) + COUNT(s25) + COUNT(s37) +
> COUNT(s49) + COUNT(s61) + COUNT(s73) + COUNT(s85)) 't1', 7 of these
> statements also filtered in WHERE statement takes 150ms thas all folks.

Would't COUNT(s1) + COUNT(s13) + COUNT(s25) + COUNT(s37) +
COUNT(s49) + COUNT(s61) + COUNT(s73) + COUNT(s85) be the same as
COUNT(s1) * 8?

Jochem

--
Jochem van Dieten
Team Macromedia Volunteer for ColdFusion, beer and fun.