Impossible or not?


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: Impossible or not?

  1. #1
    Petter Guest

    Impossible or not?


    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

  2. #2
    D. Patrick Hoerter Guest

    Re: Impossible or not?

    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



  3. #3
    Petter Guest

    Re: Impossible or not?


    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

    >



  4. #4
    D. Patrick Hoerter Guest

    Re: Impossible or not?

    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

    >>

    >



  5. #5
    Petter Guest

    Re: Impossible or not?


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

    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center