Display zero rows results on count

[former_member611771]

Hi there,

I'm trying to figure it out how to count the number of times an item was made during the current year. The SQL works well except when I have never done it on this year. This led the sql result to 0 rows and therefore the count does have nothing to count. Is there some way of handling this?

SELECT COUNT(T1."ItemCode") FROM OWOR T0 LEFT OUTER JOIN OWOR T1 ON YEAR(T1."PostDate") = YEAR(NOW()) AND T1."ItemCode" like 'ARTGRL0010'

[former_member611771]

Hi there,

I'm trying to figure it out how to count the number of times an item was made during the current year. The SQL works well except when I have never done it on this year. This led the sql result to 0 rows and therefore the count does have nothing to count. Is there some way of handling this?

SELECT COUNT(T1."ItemCode") FROM OWOR T0 LEFT OUTER JOIN OWOR T1 ON YEAR(T1."PostDate") = YEAR(NOW()) AND T1."ItemCode" like 'ARTGRL0010'

[former_member188958]

Hi Joni,

If you are looking for the count for a single item, I don't see any need for an outer join - just count the number of rows with that item code and a postdate in the current year:

SELECT COUNT(T1."ItemCode")
FROM OWOR
where YEAR(T1."PostDate") = YEAR(NOW())
AND T1."ItemCode" like 'ARTGRL0010'

Where an outer join becomes useful is if you want to generate a list of all known items with a count of how many were made this year. For that you could do something like this:

select T1."ItemCode", count( T2."PostDate" )
from
(select distinct "ItemCode" from OWOR) T1
left outer join OWOR T2 on T1."ItemCode" = T2."ItemCode"
and YEAR(T2."PostDate") = YEAR(NOW() )
group by T1."ItemCode"
go

Cheers,
-bret

[former_member611771]

Hi Bret,

Thanks for your answer.

I can count properly when the article was made that year. The SQL works well.

My problem is when the article wasn't made on that year and therefore the SQL should return 0, but since does not encounter any row, returns error or just empty.

Hope this helps to clarify the problem.

Thanks again.

[KonradZaleski]

You can generate dummy record with current year value and then cross join it with the record count. This way you will ensure that output will always be generated:

SELECT
	"CURR_YEAR",
	"ITEM_COUNT"
FROM
	(SELECT YEAR( NOW() ) AS "CURR_YEAR" FROM DUMMY) YR
	CROSS JOIN (SELECT COUNT("ItemCode") "ITEM_COUNT" FROM OWOR WHERE YEAR("PostDate") = YEAR(NOW()) AND "ItemCode" like 'ARTGRL0010') T0

[former_member611771]

Hi Konrad,

Thanks for your answer.

With this SQL I have the same problem as Bret. I cannot count 0 records.

Thanks again.

[KonradZaleski]

Not sure about your problem. This query will never return empty value. If there is no records in your table, it will return count=0.

[former_member611771]

Hi again.

The result of the SQL was like this.

In the second one you can see that does not show any result (empty). But since I want so increment one unit (to make something like a serial number), in the second one it returns me 1 as I wanted.

Thanks for the help Konrad.

Best regards