Question on grouping

  • SELECT

    AUC.AUCTION_NAME_LONG,

    CASE

    WHEN A.CLIENT_ID_CODE LIKE ('TMC')

    THEN COUNT(A.CLIENT_ID_CODE)

    ELSE NULL

    END AS TMC,

    CASE

    WHEN A.CLIENT_ID_CODE LIKE ('TOY')

    THEN COUNT(A.CLIENT_ID_CODE)

    ELSE NULL

    END AS TOY

    FROM

    ADMIN.V_REMARKT_AUTOIMS_DAILY_FACT A

    LEFT JOIN ADMIN.V_REMARKT_AUCTION_DIM AUC

    ON A.AUCTION_DIM_ID = AUC.AUCTION_DIM_ID

    WHERE

    A.SOLD_DATE BETWEEN '2018-12-01' AND '2018-12-31'

    GROUP

    BY

    AUC.AUCTION_NAME_LONG,

    A.CLIENT_ID_CODE

    the results are giving me two lines
                  TMC       TOY
    ADESA - DAL   NULL       899
    ADESA - DAL   500       NULL

    I want this all on one line?
                    TMC       TOY
    ADESA - DAL     500       899

  • thomas.miller1 - Monday, January 28, 2019 1:39 PM

    SELECT

    AUC.AUCTION_NAME_LONG,

    CASE

    WHEN A.CLIENT_ID_CODE LIKE ('TMC')

    THEN COUNT(A.CLIENT_ID_CODE)

    ELSE NULL

    END AS TMC,

    CASE

    WHEN A.CLIENT_ID_CODE LIKE ('TOY')

    THEN COUNT(A.CLIENT_ID_CODE)

    ELSE NULL

    END AS TOY

    FROM

    ADMIN.V_REMARKT_AUTOIMS_DAILY_FACT A

    LEFT JOIN ADMIN.V_REMARKT_AUCTION_DIM AUC

    ON A.AUCTION_DIM_ID = AUC.AUCTION_DIM_ID

    WHERE

    A.SOLD_DATE BETWEEN '2018-12-01' AND '2018-12-31'

    GROUP

    BY

    AUC.AUCTION_NAME_LONG,

    A.CLIENT_ID_CODE

    the results are giving me two lines
                  TMC       TOY
    ADESA - DAL   NULL       899
    ADESA - DAL   500       NULL

    I want this all on one line?
                    TMC       TOY
    ADESA - DAL     500       899

    Put your COUNT() outside of the CASE expressions and don't group by CLIENT_ID_CODE

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares - Monday, January 28, 2019 2:35 PM

    thomas.miller1 - Monday, January 28, 2019 1:39 PM

    SELECT

    AUC.AUCTION_NAME_LONG,

    CASE

    WHEN A.CLIENT_ID_CODE LIKE ('TMC')

    THEN COUNT(A.CLIENT_ID_CODE)

    ELSE NULL

    END AS TMC,

    CASE

    WHEN A.CLIENT_ID_CODE LIKE ('TOY')

    THEN COUNT(A.CLIENT_ID_CODE)

    ELSE NULL

    END AS TOY

    FROM

    ADMIN.V_REMARKT_AUTOIMS_DAILY_FACT A

    LEFT JOIN ADMIN.V_REMARKT_AUCTION_DIM AUC

    ON A.AUCTION_DIM_ID = AUC.AUCTION_DIM_ID

    WHERE

    A.SOLD_DATE BETWEEN '2018-12-01' AND '2018-12-31'

    GROUP

    BY

    AUC.AUCTION_NAME_LONG,

    A.CLIENT_ID_CODE

    the results are giving me two lines
                  TMC       TOY
    ADESA - DAL   NULL       899
    ADESA - DAL   500       NULL

    I want this all on one line?
                    TMC       TOY
    ADESA - DAL     500       899

    Put your COUNT() outside of the CASE expressions and don't group by CLIENT_ID_CODE

    Not sure I understand what you mean by put the COUNT() outside the CASE exp?

  • It's more common to use SUM(... THEN 1 ELSE 0 END), rather than COUNT, (and clearer, at least to me), so I've done that below.


    SELECT

    AUC.AUCTION_NAME_LONG,

    SUM(CASE

    WHEN A.CLIENT_ID_CODE LIKE ('TMC')

    THEN 1

    ELSE 0

    END) AS TMC,

    SUM(CASE

    WHEN A.CLIENT_ID_CODE LIKE ('TOY')

    THEN 1

    ELSE 0

    END) AS TOY

    FROM

    ADMIN.V_REMARKT_AUTOIMS_DAILY_FACT A

    LEFT JOIN ADMIN.V_REMARKT_AUCTION_DIM AUC

    ON A.AUCTION_DIM_ID = AUC.AUCTION_DIM_ID

    WHERE

    A.SOLD_DATE BETWEEN '2018-12-01' AND '2018-12-31'

    GROUP BY

    AUC.AUCTION_NAME_LONG

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • thomas.miller1 - Monday, January 28, 2019 2:53 PM

    Not sure I understand what you mean by put the COUNT() outside the CASE exp?

    He means, for example.  Where you have the following:
    CASE

    WHEN A.CLIENT_ID_CODE LIKE ('TOY')

    THEN COUNT(A.CLIENT_ID_CODE)

    ELSE NULL

    END AS TOY

    You should rewrite it as:

    COUNT(CASE

    WHEN A.CLIENT_ID_CODE LIKE ('TOY')

    THEN A.CLIENT_ID_CODE

    ELSE NULL

    END) AS TOY

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • ScottPletcher - Monday, January 28, 2019 4:01 PM

    It's more common to use SUM(... THEN 1 ELSE 0 END), rather than COUNT, (and clearer, at least to me), so I've done that below.


    SELECT

    AUC.AUCTION_NAME_LONG,

    SUM(CASE

    WHEN A.CLIENT_ID_CODE LIKE ('TMC')

    THEN 1

    ELSE 0

    END) AS TMC,

    SUM(CASE

    WHEN A.CLIENT_ID_CODE LIKE ('TOY')

    THEN 1

    ELSE 0

    END) AS TOY

    FROM

    ADMIN.V_REMARKT_AUTOIMS_DAILY_FACT A

    LEFT JOIN ADMIN.V_REMARKT_AUCTION_DIM AUC

    ON A.AUCTION_DIM_ID = AUC.AUCTION_DIM_ID

    WHERE

    A.SOLD_DATE BETWEEN '2018-12-01' AND '2018-12-31'

    GROUP BY

    AUC.AUCTION_NAME_LONG

    Gotcha, I have been staring at it for a bit, so the SUM() did not register, but I see now..ugh

  • ScottPletcher - Monday, January 28, 2019 4:01 PM

    It's more common to use SUM(... THEN 1 ELSE 0 END), rather than COUNT, (and clearer, at least to me), so I've done that below.

    I believe it's more common, because you're not introducing null values, so it doesn't produce the warning about NULLS being removed from an aggregate.  I find it clearer to use COUNT() to count

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen - Monday, January 28, 2019 4:08 PM

    thomas.miller1 - Monday, January 28, 2019 2:53 PM

    Not sure I understand what you mean by put the COUNT() outside the CASE exp?

    He means, for example.  Where you have the following:
    CASE

    WHEN A.CLIENT_ID_CODE LIKE ('TOY')

    THEN COUNT(A.CLIENT_ID_CODE)

    ELSE NULL

    END AS TOY

    You should rewrite it as:

    COUNT(CASE

    WHEN A.CLIENT_ID_CODE LIKE ('TOY')

    THEN A.CLIENT_ID_CODE

    ELSE NULL

    END) AS TOY

    Drew

    That is what I needed, the COUNT(CASE............ , I was having a brain freeze. TY

  • drew.allen - Monday, January 28, 2019 4:14 PM

    ScottPletcher - Monday, January 28, 2019 4:01 PM

    It's more common to use SUM(... THEN 1 ELSE 0 END), rather than COUNT, (and clearer, at least to me), so I've done that below.

    I believe it's more common, because you're not introducing null values, so it doesn't produce the warning about NULLS being removed from an aggregate.  I find it clearer to use COUNT() to count

    Drew

    I guess.  What if the column you want to count has NULL values in it??

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • ScottPletcher - Monday, January 28, 2019 5:06 PM

    drew.allen - Monday, January 28, 2019 4:14 PM

    ScottPletcher - Monday, January 28, 2019 4:01 PM

    It's more common to use SUM(... THEN 1 ELSE 0 END), rather than COUNT, (and clearer, at least to me), so I've done that below.

    I believe it's more common, because you're not introducing null values, so it doesn't produce the warning about NULLS being removed from an aggregate.  I find it clearer to use COUNT() to count

    Drew

    I guess.  What if the column you want to count has NULL values in it??

    Either way, the CASE expression wouldn't evaluate as true and it wouldn't be counted. You can also use a constant instead of the column.

    COUNT(CASE WHEN A.CLIENT_ID_CODE = 'TOY' THEN 1 END) AS TOY

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • ScottPletcher - Monday, January 28, 2019 5:06 PM

    drew.allen - Monday, January 28, 2019 4:14 PM

    ScottPletcher - Monday, January 28, 2019 4:01 PM

    It's more common to use SUM(... THEN 1 ELSE 0 END), rather than COUNT, (and clearer, at least to me), so I've done that below.

    I believe it's more common, because you're not introducing null values, so it doesn't produce the warning about NULLS being removed from an aggregate.  I find it clearer to use COUNT() to count

    Drew

    I guess.  What if the column you want to count has NULL values in it??

    Then your CASE expression would be different. None of the aggregate functions, by design, include NULL and that includes SUM (infact a SUM of NULLs is NULL, where as a COUNT would be 0).

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Luis Cazares - Tuesday, January 29, 2019 6:34 AM

    ScottPletcher - Monday, January 28, 2019 5:06 PM

    drew.allen - Monday, January 28, 2019 4:14 PM

    ScottPletcher - Monday, January 28, 2019 4:01 PM

    It's more common to use SUM(... THEN 1 ELSE 0 END), rather than COUNT, (and clearer, at least to me), so I've done that below.

    I believe it's more common, because you're not introducing null values, so it doesn't produce the warning about NULLS being removed from an aggregate.  I find it clearer to use COUNT() to count

    Drew

    I guess.  What if the column you want to count has NULL values in it??

    Either way, the CASE expression wouldn't evaluate as true and it wouldn't be counted. You can also use a constant instead of the column.

    COUNT(CASE WHEN A.CLIENT_ID_CODE = 'TOY' THEN 1 END) AS TOY

    It just seems less clear to me.  And that wouldn't count null values.
    SUM(CASE WHEN A.CLIENT_ID_CODE IS NULL THEN 1 ELSE 0 END) AS NULL_COUNT
    To use COUNT, you'd have to use a dummy value, just to trigger the count to occur.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • ScottPletcher - Tuesday, January 29, 2019 8:07 AM

    It just seems less clear to me.  And that wouldn't count null values.
    SUM(CASE WHEN A.CLIENT_ID_CODE IS NULL THEN 1 ELSE 0 END) AS NULL_COUNT
    To use COUNT, you'd have to use a dummy value, just to trigger the count to occur.

    As opposed to having to use a dummy value just to trigger the sum to occur?  They're not that different.
    COUNT(CASE WHEN a.Client_ID_Code IS NULL THEN 1 END) AS Null_Count

    Also, SUM is more difficult to convert to a DISTINCT COUNT than COUNT is.
    COUNT(DISTINCT CASE WHEN <Some Condition> THEN <Some Foreign/Primary Key> END)
    I wouldn't even know where to start with a SUM.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen - Tuesday, January 29, 2019 8:58 AM

    As opposed to having to use a dummy value just to trigger the sum to occur?  They're not that different.
    COUNT(CASE WHEN a.Client_ID_Code IS NULL THEN 1 END) AS Null_Count

    Also, SUM is more difficult to convert to a DISTINCT COUNT than COUNT is.
    COUNT(DISTINCT CASE WHEN <Some Condition> THEN <Some Foreign/Primary Key> END)
    I wouldn't even know where to start with a SUM.

    Drew

    You'd need to include CTE or Subquery and ROW_NUMBER most likely. That's going to get messy in my opinion. I agree that COUNT, for a conditional count, makes sense.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Thom A - Tuesday, January 29, 2019 9:07 AM

    drew.allen - Tuesday, January 29, 2019 8:58 AM

    As opposed to having to use a dummy value just to trigger the sum to occur?  They're not that different.
    COUNT(CASE WHEN a.Client_ID_Code IS NULL THEN 1 END) AS Null_Count

    Also, SUM is more difficult to convert to a DISTINCT COUNT than COUNT is.
    COUNT(DISTINCT CASE WHEN <Some Condition> THEN <Some Foreign/Primary Key> END)
    I wouldn't even know where to start with a SUM.

    Drew

    You'd need to include CTE or Subquery and ROW_NUMBER most likely. That's going to get messy in my opinion. I agree that COUNT, for a conditional count, makes sense.

    I considered that, but then you run into the possibility that the first record in the partition doesn't match the conditions, which means that you would then need to modify the ROW_NUMBER to include the conditions in the partition, if you can even do so without invalidating the results.  You're right. It does get messy.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

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

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