Results 1 to 5 of 5

Thread: Group by

  1. #1
    Join Date
    Nov 2003
    Location
    england
    Posts
    95

    Unanswered: Group by

    Hello everyone,

    I wonder if you can help me. I have developed two lots of SQL that uses the group by statement. I need to merge the two statements together. I have used a couple of sums in the select and for example:

    Select

    order.order_number,
    sum(order.order_price)

    From

    (table names)

    Where

    contract.contract_code = sh0809

    Group by
    .................

    ------------------------------------------------

    Select

    finance.finance_code
    sum(finance.finance_period)

    from

    table names

    where

    finance.finance_period = '0809'

    group by ..............
    -----------------------------------------

    The reason i have had to create to is because I want to search by contract but the contract actually has no relation to the rest of the info (i.e is not directly linked). I have to get the report to search in another table where the job is on a contract sh0809. I need the second report to give me the finance details only in '0809'. Basically I want to see what the total amount of money was spent on jobs, where the job was entered on the contract sh0809 and i want to know the total budget for '0809'. However if i went down the tracks of:

    select


    order.order_number,
    sum(order.order_price),
    sum( sum(finance.finance_period),

    from

    where

    contract.contract_code = sh0809 AND

    finance.finance_period = '0809'

    group by

    -----------------------------------------

    Do you have any ideas that when i try to merge the statements the calculations in the sums go berserk but work perfectly in separate statements?

    Thanks

    Lucy

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it's probably cross join effects

    multiple related tables multiplying their multiplicity

    push the GROUP BYs down into subqueries

    i'd help you but i can't see your table relationships
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2003
    Location
    england
    Posts
    95

    group by

    thanks for the advice, i wasn't to sure about sub queries and group by. Are you saying that purely for the sum statements use them in a sub query with a group by and then carry on as normal i.e.


    SELECT

    order.order_number,

    (select sum(order.order_value)
    from
    where
    group by .....................),

    (select sum(.....)
    from
    where
    group by.........),


    from .......

    where ...................

    lucy

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, i am
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2003
    Location
    england
    Posts
    95

    Talking group by

    Cool, thank you so much! Ill give a whirl tomorrow at work and let you know how I get on.

    Thanks again,

    Lucy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •