Results 1 to 7 of 7

Thread: rollup

  1. #1
    Join Date
    Dec 2009
    Posts
    7

    Unanswered: rollup

    hi i am new to db work.
    I have created a table. Now the users want sum for all rows at the bottom.
    How do i do it.

    here is a sample for my query

    select
    hotel_id,
    margin,
    profit,
    share,
    from
    hotels

    i need to show sum for margin, profit, share for each hotel_id under the columns margin,profit and sum

    i am using a union to get the sum but i get an error when i include the hotel_id
    please help me with this query.
    thanks in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The first problem is that your UNION is missing... There is no UNION clause in the code that you posted.

    If your error message reads:
    Quote Originally Posted by Sample Message #1
    'select' is not recognized as an internal or external command,
    operable program or batch file.
    then your problem is that you're typing the command at a command prompt instead of submitting the command to SQL Server.

    If your error message reads:
    Quote Originally Posted by Sample Message #2
    Msg 156, Level 15, State 1, Line 6
    Incorrect syntax near the keyword 'from'.
    then your problem is that you have a syntax error in the code that you posted (look just before the word "from").

    I'm sorry if this message isn't very helpful, but I'm very frustrated because you haven't given us much to work with... We have to guess at what code you actually used, what you might have done wrong that is causing an error, and what you're really trying to accomplish. If this isn't enough to help you find the answer, please post the actual code that you're using and the actual error message that you get when you try to run your code... A specific question would probably get a much more specific answer.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Dec 2009
    Posts
    7
    select
    hotel_id,
    margin,
    share,
    profit
    from
    hotels

    my union query is

    select
    margin,
    share,
    profit
    from
    hotels
    union
    select
    sum(margin) as margin,
    sum(sahre) as share,
    sum(profit) as profit
    from
    hotels

    Now when i want my table to show
    hotel_id margin share profit
    1 30 40 12
    3 25 30 9
    total 55 70 21

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your big problem is that you shouldn't be doing this in the first place.
    Column summation for reporting should be handled by your reporting tool. Use the database solely for returning data, and use your application layer for formatting and summarizing it.
    What you are doing is considered very poor design.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Dec 2009
    Posts
    7
    i have to get this repot out we do not have a reporting tool.
    I know it is a bad design and i trying to write a view to get this result.
    Please any help is appreciated
    Last edited by KETAN4U; 12-04-09 at 14:37.

  6. #6
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    select
    hotel_id,
    sum(margin) as Margin,
    sum(profit) as Profit,
    sum(share) as Share,
    from
    hotels
    group by hotel_id
    with rollup
    ?

  7. #7
    Join Date
    Dec 2009
    Posts
    7
    this is not working

    select
    hotel_id,
    sum(margin) as Margin,
    sum(profit) as Profit,
    sum(share) as Share,
    from
    hotels
    group by hotel_id
    rollup(hotel_id)
    Last edited by KETAN4U; 12-04-09 at 15:16.

Posting Permissions

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