January 28, 2019 at 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
January 28, 2019 at 2:35 pm
thomas.miller1 - Monday, January 28, 2019 1:39 PMSELECT
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 NULLI 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
January 28, 2019 at 2:53 pm
Luis Cazares - Monday, January 28, 2019 2:35 PMthomas.miller1 - Monday, January 28, 2019 1:39 PMSELECT
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 NULLI want this all on one line?
TMC TOY
ADESA - DAL 500 899Put 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?
January 28, 2019 at 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
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 28, 2019 at 4:08 pm
thomas.miller1 - Monday, January 28, 2019 2:53 PMNot 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
January 28, 2019 at 4:13 pm
ScottPletcher - Monday, January 28, 2019 4:01 PMIt'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.
SELECTAUC.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
January 28, 2019 at 4:14 pm
ScottPletcher - Monday, January 28, 2019 4:01 PMIt'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
January 28, 2019 at 4:17 pm
drew.allen - Monday, January 28, 2019 4:08 PMthomas.miller1 - Monday, January 28, 2019 2:53 PMNot sure I understand what you mean by put the COUNT() outside the CASE exp?He means, for example. Where you have the following:
CASEWHEN A.CLIENT_ID_CODE LIKE ('TOY')
THEN COUNT(A.CLIENT_ID_CODE)
ELSE NULL
END AS TOY
You should rewrite it as:
COUNT(CASEWHEN 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
January 28, 2019 at 5:06 pm
drew.allen - Monday, January 28, 2019 4:14 PMScottPletcher - Monday, January 28, 2019 4:01 PMIt'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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 29, 2019 at 6:34 am
ScottPletcher - Monday, January 28, 2019 5:06 PMdrew.allen - Monday, January 28, 2019 4:14 PMScottPletcher - Monday, January 28, 2019 4:01 PMIt'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
January 29, 2019 at 6:44 am
ScottPletcher - Monday, January 28, 2019 5:06 PMdrew.allen - Monday, January 28, 2019 4:14 PMScottPletcher - Monday, January 28, 2019 4:01 PMIt'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
January 29, 2019 at 8:07 am
Luis Cazares - Tuesday, January 29, 2019 6:34 AMScottPletcher - Monday, January 28, 2019 5:06 PMdrew.allen - Monday, January 28, 2019 4:14 PMScottPletcher - Monday, January 28, 2019 4:01 PMIt'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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 29, 2019 at 8:58 am
ScottPletcher - Tuesday, January 29, 2019 8:07 AMIt 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
January 29, 2019 at 9:07 am
drew.allen - Tuesday, January 29, 2019 8:58 AMAs 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_CountAlso, 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
January 29, 2019 at 9:37 am
Thom A - Tuesday, January 29, 2019 9:07 AMdrew.allen - Tuesday, January 29, 2019 8:58 AMAs 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_CountAlso, 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