Post reply

Aggregating a bit column

  • September 4, 2008 at 1:53 am

    #197272

    Hi,

    I have 2 tables 1 parent and one child.

    I want to select all the rows from the parent table and for each row i want to summarize a bit column in the child table.

    So, if any of the related child records contain a 1 in the bit column i would like a 1 returned otherwise a 0.

    Essentially I want to find the MAX or MIN of the column but SQL Server doesn't seem to think this is a reasonable request.

    I actually have 3 columns i'd like to summarize this way in the child table.

    Can anyone help me?

    Owen

  • September 4, 2008 at 1:57 am

    #866981

    cab u post tables structure and some sample data.

    "Keep Trying"

  • September 4, 2008 at 2:11 am

    #866985

    ParentTable

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

    ParentIDCol SomeDataCol

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

    1 information

    2 more info

    ChildTable

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

    ChildIDCol SomeChildData BitCol ParentID

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

    1 chidinfo 0 1

    2 somethign 0 1

    3 related record 1 2

    4 something important 0 2

    And what i want as my output is

    ResultTable

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

    ParentIDCol SomeDataCol BitColMax

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

    1 information 0

    2 more info 1

    So the select statment would start like:

    SELECT ParentTable.*, MAX(ChildTable.BitCol) as BitColMax......

    I have actually just solved the problem by casting my bit column to a smallint before finding the max but it seems like an inefficient way to do things.

    Is there a better way?

  • September 4, 2008 at 3:34 am

    #867016

    select ParentTable.ParentID, ParentTable.SomeDataCol,

    CASE WHEN A.Parent > 0 THEN 1 ELSE 0 END AS Bitcolumn

    FROM ParentTable

    LEFT OUTER JOIN (select parentid as parent from ChildTable where bitcol = 1

    group by parentid) A ON ParentTable.ParentID = A.Parent

    "Keep Trying"

  • September 4, 2008 at 3:59 am

    #867020

    Hi,

    unfortunately there are 3 columns on my child table i want to summarize so that query would get pretty cumbersome. I think casting the bits will probably be a more effective solution for what i want.

    Cheers

  • September 4, 2008 at 4:02 am

    #867022

    ie.

    SELECT ParentTable.*, MAX(CAST(ChildTable.bitcol1 AS smallint)), MAX(CAST(ChildTable.bitcol2 AS smallint)), MAX(CAST(ChildTable.bitcol3 AS smallint))......

  • September 8, 2008 at 12:47 am

    #868302

    Can u post the whole query?

    "Keep Trying"

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply