> ID COND1 COND2 V1 V2 V3 V4
>
> all of type int
>
> In V1 - V4 i have vlaues from 1 to 10
>
> I want to have a result with one row in which i have how many 1's and how
> many 2's ... are on the whole table

On Sat, 6 Nov 2004 20:19:20 +0200, Mitch wrote:

>Hello all.
>
>I have a table like this:
>
>ID COND1 COND2 V1 V2 V3 V4
>
>all of type int
>
>In V1 - V4 i have vlaues from 1 to 10
>
>I want to have a result with one row in which i have how many 1's and how
>many 2's ... are on the whole table

>Another question: can i have in a SELECT statement a Variable column ? Like
>variable variables ?

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:65lqo0djq181onbu8l7612bi4192d5qipd@4ax.com...
> On Sat, 6 Nov 2004 20:19:20 +0200, Mitch wrote:
>
> >Hello all.
> >
> >I have a table like this:
> >
> >ID COND1 COND2 V1 V2 V3 V4
> >
> >all of type int
> >
> >In V1 - V4 i have vlaues from 1 to 10
> >
> >I want to have a result with one row in which i have how many 1's and how
> >many 2's ... are on the whole table
>
> Hi Mitch,
>
> I know it's hard to tell from a simplified example, but the description of
> your table as well as the description of your requirements are an
> indication of an unnormalized design - you seem to have a repeating group
> in this table. If I'm correct, you can avoid lots of nasty complications
> by just moving the repeating group to a different table, with a longer
> key. (E.g. instead of having seperate columns for the results from the
> first, second, thrid and fourth test in the students table, have a results
> table with StudentNo + TestNo as compound key and test result as a third
> column).
>
> Just in case I'm totally wrong about this, or if you're really stuck with
> this design and still and still need the results, you might get lucky with
> something like this kludge:
>
> SELECT SUM(CASE WHEN V1 = 1 THEN 1 ELSE 0 END)
> + SUM(CASE WHEN V2 = 1 THEN 1 ELSE 0 END)
> + SUM(CASE WHEN V3 = 1 THEN 1 ELSE 0 END)
> + SUM(CASE WHEN V4 = 1 THEN 1 ELSE 0 END) AS NumberOfOnes,
> SUM(CASE WHEN V1 = 2 THEN 1 ELSE 0 END)
> + SUM(CASE WHEN V2 = 2 THEN 1 ELSE 0 END)
> + SUM(CASE WHEN V3 = 2 THEN 1 ELSE 0 END)
> + SUM(CASE WHEN V4 = 2 THEN 1 ELSE 0 END) AS NumberOfTwos,
> ....
> FROM YourTable
>
> (untested)
>
>
> >Another question: can i have in a SELECT statement a Variable column ?
Like
> >variable variables ?
>
> No.
>
> But if you explain what you want to accomplish (instead of only telling us
> how you want to accomplish it), provide more information about your table
> structure (as CREATE TABLE statements), sample data (as INSERT statements)
> and requested output, someone might be able to help you find a way to get
> what you want.
>
> Do check this link though: www.aspfaq.com/5006.
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)