not displaying zero in count(*)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

not displaying zero in count(*)

Author

Topic

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2007-09-18 : 06:03:09

Hi
I run this query and get:
select count(*),
ISNULL(i.invoicestatus, ist.[ID]) AS 'InvoiceStatus',
ist.[description],
ist.[ID]
from invoicestatus ist
left join invoices i on ist.[ID] = i.InvoiceStatus
group by ISNULL(invoicestatus, [ID]),
ist.[ID],
ist.[description]
order by ist.[ID]

5490 0 No Status 0
139 1 Day 21 - £10k Plus - New Call 1
49 2 Day 35 - Under £10k - New Call 2
689 3 Day 45 - Follow Up Call 3
93 4 Day 45 - Letter One (<£500) + copy invoice 4
16 5 Day 65 - Final Call + email 1 to PM 5
25 6 Day 70 - Final Demand 6
968 7 Day 77 - email 2 to PM re: legal 7
1 8 ** NOT IN USE ** Day 80 - email 3 to GB to commence Summary sheet for legal file 8
1 9 ** NOT IN USE ** Day 87 - email 4 to PM - no response 9
1 10 ** NOT IN USE ** Day 90 - email 5 to GB to issue claim 10
3 11 Payment promised 11
15 12 Under query 12
1 13 Legal 13
1 14 Paid / unpaid exception 14
1 15 No invoice number exception 15
1 16 Part promised 16
1 17 Query resolved (follow up call) 17

As soon as I add a where clause, it skips out all the zero counts.
How do I get SQL server to display a count of 0. I've tried swapping the joins and nothing seems to work:
select count(*),
ISNULL(i.invoicestatus, ist.[ID]) AS 'InvoiceStatus',
ist.[description],
ist.[ID]
from invoicestatus ist
left join invoices i on ist.[ID] = i.InvoiceStatus
where paidflag = 0
group by ISNULL(invoicestatus, [ID]),
ist.[ID],
ist.[description]
order by ist.[ID]

1808 0 No Status 0
139 1 Day 21 - £10k Plus - New Call 1
49 2 Day 35 - Under £10k - New Call 2
689 3 Day 45 - Follow Up Call 3
93 4 Day 45 - Letter One (<£500) + copy invoice 4
16 5 Day 65 - Final Call + email 1 to PM 5
25 6 Day 70 - Final Demand 6
968 7 Day 77 - email 2 to PM re: legal 7
3 11 Payment promised 11
15 12 Under query 12

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 06:10:08

ALWAYS prefix your columns with table name when having more than one table in a query.
It makes it easier for you (and us) to debug.

I assume PaidFlag is a column in the Invoices table, not InvoiceStatus table?

from invoicestatus ist
left join invoices i on ist.[ID] = i.InvoiceStatus
where paidflag = 0
from invoicestatus ist
left join invoices i on ist.[ID] = i.InvoiceStatus
and i.paidflag = 0

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-18 : 06:10:25

is [paidflag] in the [invoices] table?

If so your WHERE clause is effectively changing the LEFT JOIN into an INNER JOIN.

And if that is the case

then you could do

where paidflag = 0 OR ist.[ID] IS NULL

which will include invoices only where paidflag = 0, or where the [ID] is NULL - and assuming that cannot happen in the data itself (e.g. its a PK / NOT NULL column) then that would only happen if there was no Invoice record - i.e. omitted by the LEFT JOIN.

Probably better still would be to move the criteria to the JOIN:


FROM invoicestatus AS ist
    LEFT OUTER JOIN invoices AS i 
         ON i.InvoiceStatus = ist.[ID]
        AND i.paidflag = 0

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 06:16:41

One thing to have in mind when dealing with NULL's in the WHERE clause

-- Prepare sample data
DECLARE	@Table1 TABLE (RowID INT, Value INT)

INSERT	@Table1
SELECT	1, 1 UNION ALL
SELECT	2, 3

DECLARE	@Table2 TABLE (RowID INT, Status INT)

INSERT	@Table2
SELECT	1, 0 UNION ALL
SELECT	3, 1

-- Kristen's first query
SELECT		t1.RowID,
		t1.Value,
		t2.Status
FROM		@Table1 AS t1
LEFT JOIN	@Table2 AS t2 ON t2.RowID = t1.RowID
WHERE		t2.RowID IS NULL
		OR t2.Status = 1

-- Peso's query
SELECT		t1.RowID,
		t1.Value,
		t2.Status
FROM		@Table1 AS t1
LEFT JOIN	@Table2 AS t2 ON t2.RowID = t1.RowID
			AND t2.Status = 1

E 12°55'05.25"
N 56°04'39.16"

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2007-09-18 : 06:20:05

okay but now another issue:

select count(i.invoice_no),
ist.[description],
ist.[ID]
from invoices i
right OUTER JOIN invoicestatus AS ist
ON i.InvoiceStatus = ist.[ID]
and i.paidflag = 0
and (i.amendedfollowupdate <= getdate() or i.amendedfollowupdate is NULL)
and (i.systemfollowupdate <= getdate() or i.amendedfollowupdate < systemfollowupdate)
and ist.[ID] NOT IN (8,9,10)
group by ist.[ID],
ist.[description]
order by ist.[ID]

0 No Status 0
132 Day 21 - £10k Plus - New Call 1
49 Day 35 - Under £10k - New Call 2
688 Day 45 - Follow Up Call 3
93 Day 45 - Letter One (<£500) + copy invoice 4
16 Day 65 - Final Call + email 1 to PM 5
25 Day 70 - Final Demand 6
968 Day 77 - email 2 to PM re: legal 7
0 ** NOT IN USE ** Day 80 - email 3 to GB to commence Summary sheet for legal file 8
0 ** NOT IN USE ** Day 87 - email 4 to PM - no response 9
0 ** NOT IN USE ** Day 90 - email 5 to GB to issue claim 10
0 Payment promised 11
3 Under query 12
0 Legal 13
0 Paid / unpaid exception 14
0 No invoice number exception 15
0 Part promised 16
0 Query resolved (follow up call) 17

The ones with no status should actually be a count of 1808 but it's coming up as 0.
Also, it still seems to be including statuses of 8,9, and 10 which I omitted in the join
?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 06:23:36

Not only have you managed to rewrite query with RIGHT JOIN instead of the original LEFT JOIN, you have also managed to put the additonal wheres on the wrong place!

How about you posted some accurate and proper sample data together with your expected output?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 06:27:07

[code]SELECT COUNT(i.Invoice_No),
ist.[Description],
ist.[ID]
FROM InvoiceStatus AS ist
LEFT JOIN Invoices AS i ON i.InvoiceStatus = ist.[ID]
AND i.PaidFlag = 0
AND (i.AmendedFollowUpdate <= GETDATE() OR i.AmendedFollowUpdate IS NULL)
AND (i.SystemFollowUpdate <= GETDATE() OR i.SystemFollowUpdate > i.AmendedFollowUpdate)
WHERE ist.[ID] NOT IN (8, 9, 10)
GROUP BY ist.[ID],
ist.[description]
ORDER BY ist.[ID][/code]

E 12°55'05.25"
N 56°04'39.16"

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2007-09-18 : 06:42:04

Sorry.
Am now using:
SELECT COUNT(i.Invoice_No),
ist.[Description],
ist.[ID]
FROM InvoiceStatus AS ist
LEFT JOIN Invoices AS i ON i.InvoiceStatus = ist.[ID]
AND i.PaidFlag = 0
AND (i.AmendedFollowUpdate <= GETDATE() OR i.AmendedFollowUpdate IS NULL)
--AND (i.SystemFollowUpdate <= GETDATE() OR i.SystemFollowUpdate > i.AmendedFollowUpdate)
WHERE ist.[ID] NOT IN (8, 9, 10)
GROUP BY ist.[ID],
ist.[description]
ORDER BY ist.[ID]

In the Invoices table:
Today's date is 2007-09-18
if SystemFollowupDate is less than that it needs to be counted
if AmendedFollowUpDate is less than that it needs to be counted
AmendedFollowUpDate takes priority if it is not NULL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 06:45:25

[code]SELECT COUNT(i.Invoice_No),
ist.[Description],
ist.[ID]
FROM InvoiceStatus AS ist
LEFT JOIN Invoices AS i ON i.InvoiceStatus = ist.[ID]
AND i.PaidFlag = 0
AND (i.AmendedFollowUpdate <= CURRENT_TIMESTAMP OR i.SystemFollowUpdate <= CURRENT_TIMESTAMP)
WHERE ist.[ID] NOT IN (8, 9, 10)
GROUP BY ist.[ID],
ist.[description]
ORDER BY ist.[ID][/code]

E 12°55'05.25"
N 56°04'39.16"

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2007-09-18 : 07:04:18

These are the problem records:
SELECT --COUNT(i.Invoice_No),
*,
ist.[Description],
ist.[ID]
FROM InvoiceStatus AS ist
LEFT JOIN Invoices AS i ON i.InvoiceStatus = ist.[ID]
AND i.PaidFlag = 0
AND (i.AmendedFollowUpDate<=getdate()
OR i.AmendedFollowUpDate Is Null
OR i.SystemFollowUpDate<=getdate()
OR i.SystemFollowUpDate Is Null)
WHERE ist.[ID] NOT IN (8, 9, 10)
GROUP BY ist.[ID],
ist.[description]
ORDER BY ist.[ID]

it is picking up records where the systemfollowupdate is less than today but the amendedfollowupdate is in a few days time.
SO I somehow need to make amendedfollowupdate more important in the clauses.

?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 07:09:27

Then post a truth table matrix of how you want combinations of dates to be handled!
Help us help you! It takes a lot of time to guess what you want.

Today		AmendedFollowUpdate	SystemFollowUpdate	Is valid?
--------	-------------------	------------------	---------
20070918	20070916		NULL
20070918	20070916		20070915
20070918	20070916		20070917
20070918	20070916		20070918
20070918	20070916		20070919
20070918	NULL			NULL
20070918	NULL			20070917
20070918	NULL			20070918
20070918	NULL			20070919
20070918	20070920		NULL
20070918	20070920		20070917
20070918	20070920		20070918
20070918	20070920		20070919
20070918	20070920		20070920
20070918	20070920		20070921

E 12°55'05.25"
N 56°04'39.16"

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2007-09-18 : 07:42:48

Honestly, I would have if I could have done the formatting:

Today AmendedFollowUpdate SystemFollowUpdate Is valid?
-------- ------------------- ------------------ ---------
20070918 20070916 NULL yes
20070918 20070916 20070917 yes
20070918 20070916 20070918 yes
20070918 20070916 20070919 yes
20070918 NULL NULL only if status = 0 but then all the others would have dates otherwise
20070918 NULL 20070917 yes
20070918 NULL 20070918 yes
20070918 NULL 20070919 no
20070918 20070920 NULL no
20070918 20070920 20070917 no
20070918 20070920 20070918 no
20070918 20070920 20070919 no
20070918 20070920 20070920 no
20070918 20070920 20070921 no

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 07:46:59

quote:

Originally posted by qwertyjjj

Today		AmendedFollowUpdate	SystemFollowUpdate	Is valid?
--------	-------------------	------------------	---------
20070918	20070916		NULL			yes
20070918	20070916		20070917		yes
20070918	20070916		20070918		yes
20070918	20070916		20070919		yes
20070918	NULL			NULL			only if status = 0 but then all the others would have dates otherwise
20070918	NULL			20070917		yes
20070918	NULL			20070918		yes
20070918	NULL			20070919		no
20070918	20070920		NULL			no
20070918	20070920		20070917		no
20070918	20070920		20070918		no
20070918	20070920		20070919		no
20070918	20070920		20070920		no
20070918	20070920		20070921		no

Then this query will do.What and where is Status column?

SELECT		COUNT(i.Invoice_No),
		ist.[Description], 
		ist.[ID]
FROM		InvoiceStatus AS ist 
LEFT JOIN	Invoices AS i ON i.InvoiceStatus = ist.[ID]
			AND i.PaidFlag = 0
			AND (i.AmendedFollowUpdate <= CURRENT_TIMESTAMP OR i.AmendedFollowUpdate IS NULL)
			AND (i.SystemFollowUpdate <= CURRENT_TIMESTAMP OR i.SystemFollowUpdate IS NULL)
WHERE		ist.[ID] NOT IN (8, 9, 10)
GROUP BY	ist.[ID],
		ist.[description]
ORDER BY	ist.[ID]

E 12°55'05.25"
N 56°04'39.16"

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2007-09-18 : 07:47:21

i think:
SELECT COUNT(i.Invoice_No),
ist.[Description],
ist.[ID]
FROM InvoiceStatus AS ist
LEFT JOIN Invoices AS i ON i.InvoiceStatus = ist.[ID]
AND i.PaidFlag = 0
AND (i.AmendedFollowUpDate<=getdate()
OR (i.AmendedFollowUpDate Is Null AND i.SystemFollowUpDate<=getdate())
OR i.SystemFollowUpDate Is Null)
WHERE ist.[ID] NOT IN (8, 9, 10)
GROUP BY ist.[Description],
ist.[ID]
ORDER BY ist.[ID]

Kristen
Test

22859 Posts

Posted - 2007-09-18 : 08:32:31

"I would have if I could have done the formatting"

See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=How%20to%20format%20your%20question%20or%20a%20reply

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-18 : 19:58:48

quote:
Originally posted by Kristen

"I would have if I could have done the formatting"

See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=How%20to%20format%20your%20question%20or% color="red">20a%20reply

your highlighting is wrong ?

KH
[spoiler]Time is always against us[/spoiler]

Kristen
Test

22859 Posts

Posted - 2007-09-18 : 23:14:06

Thanks for spotting that, now fixed. There's that's the last bug fixed ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources