Impossible or not?

[Petter]

Hallo!

I'm consider myself not to bad in making SQL-queries, but this seems impossible
to do...

This is about what I like to do:

I have one table built like key1, key2, value
and four rows like:

1 1 100
1 2 10
2 1 200
2 2 50

Then I want for every leftmost key, take the value from the row where key2
= 1 and multiply it with the value from the row where key2 = 2.

The output that I want is the sum of the results from the operations. E g
(100 * 10) + (200 * 50) = 11 000

Since you can't use subqueries in aggregate functions such as Sum(...) it
seems impossible to me. I got to do this in only one query.

Someone who has an excellent solution?

-- Petter

[D. Patrick Hoerter]

Petter,

You'll have to quantify this a little more.

For example, will there only be 2 values for Column3 for a given value
of Key1?

Will this be parameterized? I.e. will we have known values for Key1,
such as '1 and 2'? If not, this is not going to be possible.

What's more - what, exactly, are you trying to do? I think you've made
the question too academic. Can you explain the actual problem?

Regards,
D. Patrick Hoerter

Petter wrote in message <3996b917$1@news.devx.com>...
>
>Hallo!
>
>I'm consider myself not to bad in making SQL-queries, but this seems
impossible
>to do...
>
>This is about what I like to do:
>
>I have one table built like key1, key2, value
>and four rows like:
>
>1 1 100
>1 2 10
>2 1 200
>2 2 50
>
>Then I want for every leftmost key, take the value from the row where key2
>= 1 and multiply it with the value from the row where key2 = 2.
>
>The output that I want is the sum of the results from the operations. E g
>(100 * 10) + (200 * 50) = 11 000
>
>Since you can't use subqueries in aggregate functions such as Sum(...) it
>seems impossible to me. I got to do this in only one query.
>
>Someone who has an excellent solution?
>
>-- Petter

[Petter]

Ok, Patrick,

I'll try to be more specific. Just wanted to remove everything unnecessary
and focus on the problem, but maby the remaining was not enough.

I'm using a kind of an application which can be used for different purposes.
In my case it is used as a sales support tool to keep sales prospects. The
application allows you to create your own datafields in order to customize
the application. Moreover you are able to connect SQL-queries to the fields
in order to get statistics etc... (But just one for each field)

So, there are two user defined fields 'Value' which means the value of a
possible order and 'Probability'. The data from these fields are stored in
the same table, and the table is connected to the sales prospects table.
(One prospect may have many user defined fields)

There is also a user defined field called 'Value total' which is the sum
of all fields 'Value' and ofcourse implemented with a SQL-query.

Now I want to create a user field like 'Balanced valued' which should summerize
all 'Value' multiplyed with corresponding 'Probability' (and moreover divided
with 100).

All data are stored in the table 'UserValues'.

So, for each prospect there is always two user defined fields e g two rows
in the table UserValues. One field with the key 'Value' and one field with
the key 'Probability'.

It means that you got the UserValue table as

Key 1, Key 2, Value

1 Value 100
1 Probability 10
2 Value 200
2 Probability 50

Key 1 is foreign key to Prospect table.

Hope I was specific enough this time and that you got it. If you have a solution
I would be very happy.

Thanks!

-- Petter

"D. Patrick Hoerter" <dphwebAT@bellatlanticDOT.net> wrote:
>Petter,
>
> You'll have to quantify this a little more.
>
> For example, will there only be 2 values for Column3 for a given value
>of Key1?
>
> Will this be parameterized? I.e. will we have known values for Key1,
>such as '1 and 2'? If not, this is not going to be possible.
>
> What's more - what, exactly, are you trying to do? I think you've made
>the question too academic. Can you explain the actual problem?
>
>Regards,
>D. Patrick Hoerter
>
>Petter wrote in message <3996b917$1@news.devx.com>...
>>
>>Hallo!
>>
>>I'm consider myself not to bad in making SQL-queries, but this seems
>impossible
>>to do...
>>
>>This is about what I like to do:
>>
>>I have one table built like key1, key2, value
>>and four rows like:
>>
>>1 1 100
>>1 2 10
>>2 1 200
>>2 2 50
>>
>>Then I want for every leftmost key, take the value from the row where key2
>>= 1 and multiply it with the value from the row where key2 = 2.
>>
>>The output that I want is the sum of the results from the operations. E
g
>>(100 * 10) + (200 * 50) = 11 000
>>
>>Since you can't use subqueries in aggregate functions such as Sum(...)
it
>>seems impossible to me. I got to do this in only one query.
>>
>>Someone who has an excellent solution?
>>
>>-- Petter
>

[D. Patrick Hoerter]

Petter,

Well, look at it as two overlaid tables, i.e. one table composed of
nothing but the rows with Value, and another with nothing but the rows with
Probability.

Next, join these two tables on the key value, and multiply as needed,
e.g.:

SELECT
[Values].[Key1],
[Values].[Value] * [Probabilities].[Value] AS CalculatedValue
FROM
[UserValues] AS [Values]
INNER JOIN [UserValues] AS [Probabilities] ON
[Probabilities].[Key1] = [Values].[Key1]
WHERE
[Values].[Key2] = 'Value' AND
[Probablities].[Key2] = 'Probability'


Run that as a start. If it needs to be expanded, let me know.

Regards,
D. Patrick Hoerter

Petter wrote in message <3996cd9b$1@news.devx.com>...
>
>Ok, Patrick,
>
>I'll try to be more specific. Just wanted to remove everything unnecessary
>and focus on the problem, but maby the remaining was not enough.
>
>I'm using a kind of an application which can be used for different
purposes.
>In my case it is used as a sales support tool to keep sales prospects. The
>application allows you to create your own datafields in order to customize
>the application. Moreover you are able to connect SQL-queries to the fields
>in order to get statistics etc... (But just one for each field)
>
>So, there are two user defined fields 'Value' which means the value of a
>possible order and 'Probability'. The data from these fields are stored in
>the same table, and the table is connected to the sales prospects table.
>(One prospect may have many user defined fields)
>
>There is also a user defined field called 'Value total' which is the sum
>of all fields 'Value' and ofcourse implemented with a SQL-query.
>
>Now I want to create a user field like 'Balanced valued' which should
summerize
>all 'Value' multiplyed with corresponding 'Probability' (and moreover
divided
>with 100).
>
>All data are stored in the table 'UserValues'.
>
>So, for each prospect there is always two user defined fields e g two rows
>in the table UserValues. One field with the key 'Value' and one field with
>the key 'Probability'.
>
>It means that you got the UserValue table as
>
>Key 1, Key 2, Value
>
>1 Value 100
>1 Probability 10
>2 Value 200
>2 Probability 50
>
>Key 1 is foreign key to Prospect table.
>
>Hope I was specific enough this time and that you got it. If you have a
solution
>I would be very happy.
>
>Thanks!
>
>-- Petter
>
>"D. Patrick Hoerter" <dphwebAT@bellatlanticDOT.net> wrote:
>>Petter,
>>
>> You'll have to quantify this a little more.
>>
>> For example, will there only be 2 values for Column3 for a given value
>>of Key1?
>>
>> Will this be parameterized? I.e. will we have known values for Key1,
>>such as '1 and 2'? If not, this is not going to be possible.
>>
>> What's more - what, exactly, are you trying to do? I think you've made
>>the question too academic. Can you explain the actual problem?
>>
>>Regards,
>>D. Patrick Hoerter
>>
>>Petter wrote in message <3996b917$1@news.devx.com>...
>>>
>>>Hallo!
>>>
>>>I'm consider myself not to bad in making SQL-queries, but this seems
>>impossible
>>>to do...
>>>
>>>This is about what I like to do:
>>>
>>>I have one table built like key1, key2, value
>>>and four rows like:
>>>
>>>1 1 100
>>>1 2 10
>>>2 1 200
>>>2 2 50
>>>
>>>Then I want for every leftmost key, take the value from the row where
key2
>>>= 1 and multiply it with the value from the row where key2 = 2.
>>>
>>>The output that I want is the sum of the results from the operations. E
>g
>>>(100 * 10) + (200 * 50) = 11 000
>>>
>>>Since you can't use subqueries in aggregate functions such as Sum(...)
>it
>>>seems impossible to me. I got to do this in only one query.
>>>
>>>Someone who has an excellent solution?
>>>
>>>-- Petter
>>
>

[Petter]

Magic!!

It worked exactly as I wanted. The final query ended up like :

SELECT SUM((CAST(values.value as int) * CAST(prob.value as int) / 100) as
CalcValue ...

Patrick, I guess I own you a couple of beer next time you passes Stokholm.

Thanks!

-- Petter

"D. Patrick Hoerter" <dphwebAT@bellatlanticDOT.net> wrote:
>Petter,
>
> Well, look at it as two overlaid tables, i.e. one table composed of
>nothing but the rows with Value, and another with nothing but the rows with
>Probability.
>
> Next, join these two tables on the key value, and multiply as needed,
>e.g.:
>
> SELECT
> [Values].[Key1],
> [Values].[Value] * [Probabilities].[Value] AS CalculatedValue
> FROM
> [UserValues] AS [Values]
> INNER JOIN [UserValues] AS [Probabilities] ON
> [Probabilities].[Key1] = [Values].[Key1]
> WHERE
> [Values].[Key2] = 'Value' AND
> [Probablities].[Key2] = 'Probability'
>
>
> Run that as a start. If it needs to be expanded, let me know.
>
>Regards,
>D. Patrick Hoerter
>
>Petter wrote in message <3996cd9b$1@news.devx.com>...
>>
>>Ok, Patrick,
>>
>>I'll try to be more specific. Just wanted to remove everything unnecessary
>>and focus on the problem, but maby the remaining was not enough.
>>
>>I'm using a kind of an application which can be used for different
>purposes.
>>In my case it is used as a sales support tool to keep sales prospects.
The
>>application allows you to create your own datafields in order to customize
>>the application. Moreover you are able to connect SQL-queries to the fields
>>in order to get statistics etc... (But just one for each field)
>>
>>So, there are two user defined fields 'Value' which means the value of
a
>>possible order and 'Probability'. The data from these fields are stored
in
>>the same table, and the table is connected to the sales prospects table.
>>(One prospect may have many user defined fields)
>>
>>There is also a user defined field called 'Value total' which is the sum
>>of all fields 'Value' and ofcourse implemented with a SQL-query.
>>
>>Now I want to create a user field like 'Balanced valued' which should
>summerize
>>all 'Value' multiplyed with corresponding 'Probability' (and moreover
>divided
>>with 100).
>>
>>All data are stored in the table 'UserValues'.
>>
>>So, for each prospect there is always two user defined fields e g two rows
>>in the table UserValues. One field with the key 'Value' and one field with
>>the key 'Probability'.
>>
>>It means that you got the UserValue table as
>>
>>Key 1, Key 2, Value
>>
>>1 Value 100
>>1 Probability 10
>>2 Value 200
>>2 Probability 50
>>
>>Key 1 is foreign key to Prospect table.
>>
>>Hope I was specific enough this time and that you got it. If you have a
>solution
>>I would be very happy.
>>
>>Thanks!
>>
>>-- Petter
>>
>>"D. Patrick Hoerter" <dphwebAT@bellatlanticDOT.net> wrote:
>>>Petter,
>>>
>>> You'll have to quantify this a little more.
>>>
>>> For example, will there only be 2 values for Column3 for a given value
>>>of Key1?
>>>
>>> Will this be parameterized? I.e. will we have known values for Key1,
>>>such as '1 and 2'? If not, this is not going to be possible.
>>>
>>> What's more - what, exactly, are you trying to do? I think you've
made
>>>the question too academic. Can you explain the actual problem?
>>>
>>>Regards,
>>>D. Patrick Hoerter
>>>
>>>Petter wrote in message <3996b917$1@news.devx.com>...
>>>>
>>>>Hallo!
>>>>
>>>>I'm consider myself not to bad in making SQL-queries, but this seems
>>>impossible
>>>>to do...
>>>>
>>>>This is about what I like to do:
>>>>
>>>>I have one table built like key1, key2, value
>>>>and four rows like:
>>>>
>>>>1 1 100
>>>>1 2 10
>>>>2 1 200
>>>>2 2 50
>>>>
>>>>Then I want for every leftmost key, take the value from the row where
>key2
>>>>= 1 and multiply it with the value from the row where key2 = 2.
>>>>
>>>>The output that I want is the sum of the results from the operations.
E
>>g
>>>>(100 * 10) + (200 * 50) = 11 000
>>>>
>>>>Since you can't use subqueries in aggregate functions such as Sum(...)
>>it
>>>>seems impossible to me. I got to do this in only one query.
>>>>
>>>>Someone who has an excellent solution?
>>>>
>>>>-- Petter
>>>
>>
>