Count(*) output blank but required Zero
-
HI All,
I had an requirement to create count of numbers for every 30 min with few filter condition on where filed with group by DayDate and Half an Hour Time.However the count is correct when data is available but when the count is blank for particular day and time, but how would I get zero there
instead of blank.Eg Query:
SELECT
'[ID- 1411]+ ',' + cast(Count(Number of Items))+ ',' + [Date Field] + ':'+ '[Half Hour Time Field]'
from [dbo].FCTtable
inner join dbo.dimdate dates on dates.DayDateKEY = i.[DayDateKEY]
inner join dbo.DIMtime tim on tim.DIMtimeKEY = i.[DIMtimeKEY]
INNER JOIN dbo.DIMcountry contr ON contr.DIMcountryKEY = i.DIMCountryEY
where dates.DayDateKEY > '20160224' -- YYYYMMDD
and contr.country = 'US'
group by [DateField],[Half hour Time Field]The Desired Output
ID-1411, 5, 260224:00:00:00
ID-1411, 3, 260224:00:30:00
--------------- ID-1411, 0, 260224:01:00:00 --- This line is missing in my output
ID-1411,14, 260224:01:30:00.I have tried to change the above query to left joins instead of inner join but still no luck, could any help me with this query.
SELECT
'[ID- 1411]+ ',' + cast(Count(Number of Items))+ ',' + [Date Field] + ':'+ '[Half Hour Time Field]'
from [dbo].FCTtable
left join dbo.dimdate dates on dates.DayDateKEY = i.[DayDateKEY]
left join dbo.DIMtime tim on tim.DIMtimeKEY = i.[DIMtimeKEY]
INNER JOIN dbo.DIMcountry contr ON contr.DIMcountryKEY = i.DIMCountryEY
where dates.DayDateKEY > '20160224' -- YYYYMMDD
and contr.country = 'US'
group by [DateField],[Half hour Time Field]Many Thanks in Advance.
-
Without actual sample data, this is a little difficult, my answer is guess work.
My guess, however, is that you're starting in the wrong place. I assuming that your table, FCTtable, doesn't have a value for ID-1411 at 01:00:00. As that's your base table, it doesn't matter what (left) joins you're doing, you're never going to return a result (well unless you use a FULL OUTER JOIN, but I don't think that's your intention/solution here).
Edit: Also, the SQL you have provided won't run, the entire thing is encapsulated in quotations. You have a CAST without an AS, and you also reference a table 'i', but there is no table, or alias 'i'. I was attempting to give you a solution, however, I don't want to second guess so please at least fix your SQL first.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Placing the filter on dates to WHERE clause turns LEFT JOIN to INNER JOIN.
Move it up, the the join condition:
SELECT
'[ID- 1411]+ ',' + cast(Count(Number of Items))+ ',' + [Date Field] + ':'+ '[Half Hour Time Field]'
from [dbo].FCTtable
left join dbo.dimdate dates on dates.DayDateKEY = i.[DayDateKEY]
and dates.DayDateKEY > '20160224' -- YYYYMMDD
left join dbo.DIMtime tim on tim.DIMtimeKEY = i.[DIMtimeKEY]
INNER JOIN dbo.DIMcountry contr ON contr.DIMcountryKEY = i.DIMCountryEY
where contr.country = 'US'
group by [DateField],[Half hour Time Field]_____________
Code for TallyGenerator -
Hi
Yes, ID-1411 is not a field from fact table, its a string, I trying to get output with along with string and also the date condtion is on FactTable DateTime Column Field not an Date Table Field, my apologies for earlier mis interpretation.
Correct Query
SELECT
'[ID- 1411]+ ',' + cast(Count(Number of Items))+ ',' + date.[Date Field] + ':'+ Tim[Half Hour Time Field] as ID
from [dbo].FCTtable i
inner join dbo.dimdate dates on dates.DayDateKEY = i.[DayDateKEY]
inner join dbo.DIMtime tim on tim.DIMtimeKEY = i.[DIMtimeKEY]
INNER JOIN dbo.DIMcountry contr ON contr.DIMcountryKEY = i.DIMCountryEY
where i.FactTableDateTimeColumn > '20160224070600' -- YYYYMMDDHHMMSS
and contr.country = 'US'
group by [DateField],[Half hour Time Field] -
A field in your SELECT returns a NULL and thus the entire concatenated line is NULL.
Replacing "cast(Count(Number of Items))" by "cast(COALESCE(Count(Number of Items), 0) as varchar)" would do the trick ...
Good luck! - Sangeeth878787 - Monday, February 27, 2017 12:37 AM
There are a lot of things still wrong with that query. Please test and try again. I'm happy to help, but please actually check your work so far. The problems I've spotted:
String encapsulation is wrong
What are you casting to
A field with spaces (no square brackets)
Missing period on table/column declaration.if you supply bad SQL to us, we may not end up fixing it for your environment. Also test your sample SQL, before you post it.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - rot-717018 - Monday, February 27, 2017 1:40 AM
Nah, it's not right.
There are many problems with this query, but that one is not one of them.Check this out:
select COUNT(nullif(1,1))
GO
declare @I int
select COUNT(@i)GO
select COUNT(1)
where 1 = 0COUNT never returns NULL
_____________
Code for TallyGenerator - Sergiy - Monday, February 27, 2017 4:05 AM
yah you're right, I forgot --> COUNT never returns NULL ... thanks for pointing this out 🙂
Back to Sangeeth878787's question, a blank line on the output of concatenated columns is typically due of a column returning NULL, so some column in the query must return NULL ...
- rot-717018 - Monday, February 27, 2017 6:24 AM
The OP isn't getting a blank line, they aren't getting the line. I.e. They want to show a count (of 0) where data does not exist. This should be easily solved by changing the base table and the order of the JOINs.
As the OP hasn't fixed their SQL, I've left their errors in with my proposed solution (and copied them if I refer to the same field/table). I would like them to learn from their mistakes, and how to fix them.
SELECT '[ID- 1411]+ ',' + cast(Count(Number of Items))+ ',' + date.[Date Field] + ':'+ Tim[Half Hour Time Field] as ID
FROM dbo.dimdate dates on dates.DayDateKEY = i.[DayDateKEY]
CROSS APPLY dbo.DIMtime tim
LEFT JOIN dbo.FCTtable i ON dates.DayDateKEY = i.[DayDateKEY]
AND tim.DIMtimeKEY = i.[DIMtimeKEY]
LEFT JOIN dbo.DIMcountry contr ON contr.DIMcountryKEY = i.DIMCountryEY
AND contr.country = 'US'
where dates.DayDateKEY > '20160224070600' -- YYYYMMDDHHMMSS
group by date.DateField,
Tim[Half Hour Time Field];Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - rot-717018 - Monday, February 27, 2017 1:40 AMThom A - Monday, February 27, 2017 6:36 AM
Hi Thom,
As you said exactly, I want to show a count (of 0) where data does not exist. This should be easily solved by changing the base table and the order of the JOINs. But when I used Cross apply, the expected count is not same as using Left or inner joins. But output is correct when I use left or inner join but no line where data does not exist instead on Zero - Sangeeth878787 - Monday, February 27, 2017 9:04 AM
Can you post the SQL you're using (mine won't work, so i'd like to see what it is you're running)? My solution should return the right result, if used correctly, and the syntax is valid.
Edit: Sorry, a slight correction, due to country JOIN (why supplying valid SQL and consumable sample data is so important):
SELECT '[ID- 1411]+ ',' + cast(Count(Number of Items))+ ',' + date.[Date Field] + ':'+ Tim[Half Hour Time Field] as ID
FROM dbo.dimdate dates on dates.DayDateKEY = i.[DayDateKEY]
CROSS APPLY dbo.DIMtime tim
LEFT JOIN dbo.FCTtable i ON dates.DayDateKEY = i.[DayDateKEY]
AND tim.DIMtimeKEY = i.[DIMtimeKEY]
LEFT JOIN dbo.DIMcountry contr ON contr.DIMcountryKEY = i.DIMCountryEY
where dates.DayDateKEY > '20160224070600' -- YYYYMMDDHHMMSS
AND (contr.country = 'US' OR contr.country IS NULL)
group by date.DateField,
Tim[Half Hour Time Field];
If the above doesn't work (after fixing the errors), please post what the SQL your running is, and some consumable data.Thanks.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
THOM Query
SELECT
--'[ID1411]' +','+ cast([DayDateKEY] as nvarchar(500))+':'+cast([Half Hour Start Time] as nvarchar(500))+', '+cast(count(I.[Call Number]) as nvarchar(105)) as ID
from dimdate dates --on dates.DayDateKEY = i.[Call Date DayDateKEY]
cross apply DIMtime tim
left join .FCTincident I on dates.DayDateKEY = i.[DayDateKEY] and
tim.DIMtimeKEY = i.[Call Start DIMtimeKEY]
left JOIN conformedDimension.DIMcountry contr ON contr.DIMCountryKey= i.DIMCountryKey
where
[contr].[Country] = 'US'
AND
i.[Time At Call] > '2017-02-27 14:35:32.697'
group by [DayDateKEY],[Half Hour Start Time]MY Query
SELECT
'[ID1411]' +','+ cast([DayDateKEY] as nvarchar(500))+':'+cast([Half Hour Start Time] as nvarchar(500))+', '+cast(count(I.[Call Number]) as nvarchar(105)) as ID
from dimdate dates
left join [999all].FCTincident I on dates.DayDateKEY = i.[DayDateKEY]
left join conformedDimension.DIMtime tim on tim.DIMtimeKEY = i.[DIMtimeKEY]
INNER JOIN DIMcountry contr ON contr.DIMCounrytKEY = i.DIMCountryKEY
where
[contr].[Country Name] = 'US'
AND
i.[Time at Call] > '2017-02-27 14:35:32.697'
group by [DayDateKEY],[Half Hour Start Time]
Output:
In the both cases when there is no data for 18:00:00 then output is
But my Desired Output [ID1411],2017-02-27:18:00:00, 0 -- Count Zero.
It is usually we don't get any output for count if there is no data corresponding to it, but I am looking to show as Zero instead of no Output.
Many Thanks
-
That isn't my query, at all. I don't refer to i.[Time at Call] > '2017-02-27 14:35:32.697' in my where clause, and my reference to the country is in an OR statement with an IS NULL.
As Sergiy rightly pointed out, including a field in the WHERE clause turns a LEFT JOIN into an INNER JOIN.
This is how your query should look, after fixing your initial problems (note that you will need to give a valid field for the count):
SELECT '[ID- 1411]' + ',' + cast(Count(i.somefield AS varchar(100)))+ ',' + date.[Date Field] + ':'+ Tim.[Half Hour Time Field] as ID
FROM dbo.dimdate dates
CROSS APPLY dbo.DIMtime tim
LEFT JOIN dbo.FCTtable i ON dates.DayDateKEY = i.[DayDateKEY]
AND tim.DIMtimeKEY = i.[DIMtimeKEY]
LEFT JOIN dbo.DIMcountry contr ON contr.DIMcountryKEY = i.DIMCountryEY
where dates.DayDateKEY > '20160224070600' -- YYYYMMDDHHMMSS
AND (contr.country = 'US' OR contr.country IS NULL)
group by date.DateField,
Tim.[Half Hour Time Field];Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
It is always good to have some sample data for better clarity.
Maybe this will help wrap your head around problem. It is a simple illustration that you can build off of to put together your solution or understand the solution that has been provided in the last post.
Take a look at the concept here (in here I you can substitute your dates table for my calendar one and adjust the actual dates as needed).
create table #someFact
(fkey int identity,
[Date] date,
amount decimal(10,2)
) insert into #someFact
select '1970-01-01',5.2 union all
select '1970-01-01',5.2 union all
select '1970-01-02',5.2 union all
select '1970-01-02',5.2 union all
select '1970-01-04',5.2 union all
select '1970-01-04',5.2 union all
select '1970-01-05',5.2 union all
select '1970-01-05',5.2 union all
select '1970-01-07',5.2 union all
select '1970-01-07',5.2 select * From #someFact-- just to look
select c.[Date],
isnull(sum(f.amount),0.0) as totalAmount
from calendar as c
left join #someFact as f ON c.[date] =f.[Date]
where c.[date]<='19700108'
group by c.[Date]-- your solution will look similar to :
select cast(c.[Date] as varchar(20))+'-->'+
cast(isnull(sum(f.amount),0.0) as varchar(20))
from calendar as c
left join #someFact as f ON c.[date] =f.[Date]
where c.[date]<='19700108'
group by c.[Date]----------------------------------------------------
- MMartin1 - Monday, February 27, 2017 2:16 PM
Hi Martin,
I am glad for message, but when there is no data associated, the count will not return null, so isnull function won't work here. From the script you provided here, it will show count or sum of amount for 1970-01-01 to 1970-01-05 and 1970-01-06 but not for 1970-01-06. Please correct me If i am wrong.
Thank you
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply