Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2011
    Posts
    1

    Question Unanswered: Group by clause- hide column name

    Hi
    I have a table named t_food.

    In that, my fields are " food_id ,food_name , food_qty , food_nature, food_price "

    and i want to list all food items with sum of its quantites . I'm using group by clause like this .


    select food_id , food_name , sum(food_qty) as qty, food_nature,food_price from t_food group by

    food_id,food_name,food_nature , food_price ;

    but what i want my query as

    select food_id , food_name , sum(food_qty) as qty, food_nature,food_price from t_food group by food_id ;

    Is there anyway to group by database items by omitting/hiding column names ..?

    Thanks in advance...!
    Last edited by SanCJ; 01-06-11 at 09:57.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    I don't really understand you question.

    Please:

    post some sample data (preferrably as INSERT INTO statements)
    post the desired outcome based on that sample data
    and make sure to use [code] tags to make your SQL readable

  3. #3
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    (and, offhand, it appears that the database should be normalized...)

    It looks like there's food information, and inventory information, and pricing information, all stored in one table???
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by SanCJ View Post
    and i want to list all food items with sum of its quantites
    there's your problem right there

    you can't show detailed and aggregate information at the same time

    you can either list all food items, or you can show the sum, but not both -- at least, not without a lot more explanation on your part about ~which~ sum you want to show for ~each~ item
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by r937 View Post
    you can't show detailed and aggregate information at the same time
    Not entirely true

    Code:
    SELECT food_id, 
           food_name, 
           sum(food_qty) over (partition by food_nature) as nature_qty, 
           food_nature,
           food_price 
    FROM t_food;
    Last edited by shammat; 01-10-11 at 17:56. Reason: Removed the senseless grouping by PK as pointed out by r937

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by shammat View Post
    Not entirely true
    we can get into a nice discussion on several tangents here...

    but suffice to say that food_id is presumably the primary key of that table, so partitioning over it will give only one row, hence the sum isn't any different from the food_qty
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by r937 View Post
    but suffice to say that food_id is presumably the primary key of that table, so partitioning over it will give only one row, hence the sum isn't any different from the food_qty
    Good point

    Consider it an example

Tags for this Thread

Posting Permissions

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