Results 1 to 5 of 5

Thread: Group By Issue

  1. #1
    Join Date
    Mar 2004
    Posts
    33

    Unhappy Unanswered: Group By Issue

    I was asked to display two fields plus a total counts of the table but only need to group by one field and leave the other field as Display field. For example:

    Select A, B, Count(*)
    From Table
    where ...
    Group By B

    It won't work on SQL unless you group both fields. Any way to get around this?

    Thanks!

    J827

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Think about it, and you'll see that it makes no logical sense.

    If value A remains constant for any value B, then go ahead and group by A, as it will not affect your output.

    Select A, B, Count(*)
    From Table
    where ...
    Group By A, B

    If value A varies for any given value B, then which value are you going to show in your output? You need some type of criteria for deciding. You could, for instance, use the lowest value of A:

    Select min(A) as A, B, Count(*)
    From Table
    where ...
    Group By B

    I think you need to better define, or at least better explain, what your objective is.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Mar 2004
    Posts
    33

    Unhappy Group By Issue

    blindman,

    Thank you for your quick response!

    The value A actually is coming from different table and it is not a constant for any value B. If not grouping B, they are just part of combination running results based on the business logic but my business partner would like to display both fields in the report for no calculations should be run off of A field. Is this feasible or not?

    J827

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Calculate your B totals in a subquery:

    select TableA.A, Subquery.B, Subquery.RecordCount
    from TableA
    left outer join (select B, count(*) as RecordCount from TableB group by B) Subquery
    on TableA.B = Subquery.B
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    GROUP BY can return one row for each column you specify in the GROUP BY clause, plus any additional aggregates of that group as a column.

    So if you have TableA, with columns Title, Sales, Price, a valid GROUP BY would be:

    SELECT Title, SUM(Sales) AS Sales, MAX(PRICE) AS MaxPrice, (SUM(SALES) * (SUM(PRICE)) AS Total FROM TableA GROUP BY Title

    If the values for ValueB are constant with respect to a specfic value of ValueA, then you can kinda fudge this by using a MIN or MAX function. In which case you don't need to include the column in the GROUP BY clause because MIN and MAX are aggregate functions.
    That which does not kill me postpones the inevitable.

Posting Permissions

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