locked
Trying to understand GroupBy RRS feed

  • Question

  • I have this query that return like 500 rows all the information i need to see based on my grouping in a table....

     

    Select sti.parent_topic_id, sti.sort_order, count(sort_order)

    From sam_topic_items sti

    Inner Join sam_topic st On sti.topic_id = st.id

    Group by parent_topic_id, sort_order

    Having count(sort_order) > 1

    Order By parent_topic_id

     

    Now I have the same query but i want to pull details about my grouping like the name and some other stuff but it only pulls two quereys why is that...

    SELECT

    st.description_short as 'name',

    st.description_definition as 'description',

    'TOPIC' as 'type',

    sti.sort_order as 'sort_order',

    st.is_required,

    st.is_active,

    st.template_filter_at_overview,

    sti.parent_topic_id as 'parent_topic_id',

    count(sti.sort_order)

    FROM

    SAM_topic st

    LEFT OUTER JOIN

    SAM_topic_items sti

    ON

    st.[id] = sti.topic_id

    Group By sti.parent_topic_id, sti.sort_order,st.description_short,

    st.description_definition,

    sti.sort_order,

    st.is_required,

    st.is_active,

    st.template_filter_at_overview,

    sti.parent_topic_id

    Having count(sti.sort_order) > 1

    Order By parent_topic_id

    Friday, March 16, 2007 9:32 PM

Answers

  • Use your original query as a derived table and then join that derived table to the table 'SAM_topic_Items'.

    SELECT
       sti.COLUMN1,
       sti.Column2,
       dt.Column2,
       dt.Column3,
       sti.etc
    FROM SAM_topic_items sti
       JOIN (SELECT ... 
             GROUP BY ... 
            ) MyDerivedTable dt
          ON sti.ParentTopicID = dt.ParentTopicID

    Friday, March 16, 2007 9:42 PM
  • Your query is a bit of a departure from the suggestion I provided. I recommend that you review my suggestion, especially how to use a derived table, a derived table in a JOIN, and the use of table aliases.

    Relative to your errors:

    Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier "Owners.OwnersName" could not be bound.

    There is no table known by the name of [Owners] in the outer query. There is a derived table known by the name [x] -that table comes from a query to the table [Owners], but the outer query does not know that.

    Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier "ItemCategories.Value" could not be bound.

    There is no table known by the name [temCategories] in either the inner or the outer query. Perhaps you meant to include [temCategories] in the JOIN, but it is NOT there.

    Etc.

    Saturday, March 17, 2007 4:23 AM

All replies

  • Use your original query as a derived table and then join that derived table to the table 'SAM_topic_Items'.

    SELECT
       sti.COLUMN1,
       sti.Column2,
       dt.Column2,
       dt.Column3,
       sti.etc
    FROM SAM_topic_items sti
       JOIN (SELECT ... 
             GROUP BY ... 
            ) MyDerivedTable dt
          ON sti.ParentTopicID = dt.ParentTopicID

    Friday, March 16, 2007 9:42 PM
  • By introducing additional columns into your GROUP BY clause, you are likely to be increasing the number of distinct rows that your query will return (ignore the aggregate for the moment).

    This means that once the aggregate has been calculated there will be fewer rows that meet the criteria specified in the HAVING clause.

    Try running the original and modified queries but without the HAVING clause to see what I mean.

    Chris

    Friday, March 16, 2007 9:44 PM
  • I ran it without the having clause and it returns all the data but some of this data i dont need....

     

    SELECT

    sti.parent_topic_id as 'parent_topic_id',

    sti.sort_order,

    st.description_short as 'name',

    st.description_definition as 'description',

    'TOPIC' as 'type',

    sti.sort_order as 'sort_order',

    st.is_required,

    st.is_active,

    st.template_filter_at_overview

    FROM

    SAM_topic st

    LEFT OUTER JOIN

    SAM_topic_items sti

    ON

    st.[id] = sti.topic_id

    Group By sti.parent_topic_id,

    sti.sort_order,

    st.description_short,

    st.description_definition,

    sti.sort_order,

    st.is_required,

    st.is_active,

    st.template_filter_at_overview,

    sti.parent_topic_id

    Having count(sti.sort_order) > 1

    Order By parent_topic_id

     

     

    What do i need to do to make this work with the group by??? I really dont understand how bringing in columns from another table would effect my result in such a way....

    Friday, March 16, 2007 9:50 PM
  • You are filtering on 'duplicate rows' as in HAVING COUNT(*) > 1

    This count is applied to *all columns in the select list*, therefore when you add more columns, then the number of 'dupe rows' may change.

    Arnie has provided an answer above on how you can achieve what you want.

    It's basically like this;

    SELECT a.extracol1, a.extracol2, x.col1, x.col2
    FROM ( 
           SELECT col1, col2
           FROM someTab
           GROUP BY col1, col2
           HAVING COUNT(*) > 1 
         ) x
    JOIN someTab a
    ON   x.col1 = a.col1
    AND  x.col2 = b.col2

    /Kenneth

    Saturday, March 17, 2007 1:34 AM
  • I tried to do it that way with this query and this is the result I got.....

     

    Select Owners.OwnersName, ItemCategories.Value

    From

    (

    Select Owners.OwnersName, ItemCategories.Value, count(ItemCategories.Value)

    From HomeItems

    Group By Owners.OwnersName, ItemCategories.Value

    Having Count(ItemCategories.Value) > 1

    ) x

    Join HomeItems On x.Owners.Owner = HomeItems.Owner

    And ItemCategories.ItemCategoryID = HomeItems.CategoryID

     

    And here is all the errors

     

    Msg 4104, Level 16, State 1, Line 1

    The multi-part identifier "Owners.OwnersName" could not be bound.

    Msg 4104, Level 16, State 1, Line 1

    The multi-part identifier "ItemCategories.Value" could not be bound.

    Msg 4104, Level 16, State 1, Line 1

    The multi-part identifier "ItemCategories.Value" could not be bound.

    Msg 4104, Level 16, State 1, Line 1

    The multi-part identifier "Owners.OwnersName" could not be bound.

    Msg 4104, Level 16, State 1, Line 1

    The multi-part identifier "ItemCategories.Value" could not be bound.

    Msg 4104, Level 16, State 1, Line 1

    The multi-part identifier "ItemCategories.Value" could not be bound.

    Msg 8155, Level 16, State 2, Line 1

    No column was specified for column 3 of 'x'.

    Msg 207, Level 16, State 1, Line 10

    Invalid column name 'Owners'.

    Msg 4104, Level 16, State 1, Line 10

    The multi-part identifier "ItemCategories.ItemCategoryID" could not be bound.

    Msg 4104, Level 16, State 1, Line 10

    The multi-part identifier "Owners.OwnersName" could not be bound.

    Msg 4104, Level 16, State 1, Line 10

    The multi-part identifier "ItemCategories.Value" could not be bound.

     

     

     

     

    Saturday, March 17, 2007 4:05 AM
  • Your query is a bit of a departure from the suggestion I provided. I recommend that you review my suggestion, especially how to use a derived table, a derived table in a JOIN, and the use of table aliases.

    Relative to your errors:

    Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier "Owners.OwnersName" could not be bound.

    There is no table known by the name of [Owners] in the outer query. There is a derived table known by the name [x] -that table comes from a query to the table [Owners], but the outer query does not know that.

    Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier "ItemCategories.Value" could not be bound.

    There is no table known by the name [temCategories] in either the inner or the outer query. Perhaps you meant to include [temCategories] in the JOIN, but it is NOT there.

    Etc.

    Saturday, March 17, 2007 4:23 AM
  • Opps I did get it to work doing it your way but I had to use Inner Joins....see...

     

    Select ItemCategories.Value, Owners.OwnerName, Count(Owners.OwnerName) as 'Owned Items' From HomeItems

    Inner Join ItemCategories ON HomeItems.CategoryID = ItemCategories.ItemCategoryID

    Inner Join Owners On HomeItems.Owner = Owners.OwnerID

    Group By ItemCategories.ItemCategoryID, ItemCategories.Value, Owners.OwnerName

    Having count(ItemCategories.Value) > 1

     

    Order By

    ItemCategories.ItemCategoryID

     

    This returns me a result set of What owners own what "type" of things and how many of that "type" they own.....the only thing is that it only returns this set of data for one owner...I have many owners but it only returns the result set for one particular owner??? Do you see anything in the query that would make this sort of behavoir???

     

    Saturday, March 17, 2007 4:38 AM
  • Thanks People...That way you should me was correct Thanks!! It was something wrong with how i had my joins working thanks!!!
    Saturday, March 17, 2007 11:14 PM