how to return zeros with a group by that returns nulls

Question

Post reply

how to return zeros with a group by that returns nulls

Viewing 9 posts - 1 through 10 (of 10 total)

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

gsc_dba SSCertifiable Points: 5407 More actions · Answer 1

Try the ISNULL function:

DECLARE @nullablecolumn INT = NULL

SELECT ISNULL(@nullablecolumn, 0)

gsc_dba

ddnikon SSC-Addicted Points: 489 More actions · Answer 2

Have tried ISNULL , doesn't work, still returns null

ddnikon SSC-Addicted Points: 489 More actions · Answer 3

when no rows are returned for invoice expense and invoice tax, I still need a row to be returned with the invoice key from invoice and a count of zero

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

Difficult to answer your question with the data you've provided. See the below examples for how to work it out yourself.

DECLARE @TABLE AS TABLE(The_Group INT)

INSERT INTO @TABLE

SELECT 1

UNION ALL SELECT 2

UNION ALL SELECT 3

DECLARE @DATA_TABLE AS TABLE (ID INT IDENTITY, The_Group INT, Data CHAR(1))

INSERT INTO @DATA_TABLE

SELECT 1, 'A'

UNION ALL SELECT 2, 'A'

--How you're doing it --> misses out the "0" for The_Group 3

SELECT a.The_Group, COUNT(*) AS cnt

FROM @TABLE a

INNER JOIN @DATA_TABLE b ON a.The_Group = b.The_Group

GROUP BY a.The_Group

--Include the "0"

SELECT a.The_Group, COALESCE(b.Cnt,0) AS Cnt

FROM (SELECT

The_Group

FROM @TABLE

GROUP BY The_Group) a

LEFT JOIN (SELECT a.The_Group, COUNT(*) AS cnt

FROM @TABLE a

INNER JOIN @DATA_TABLE b ON a.The_Group = b.The_Group

GROUP BY a.The_Group) b ON a.The_Group = b.The_Group

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Sean Lange SSC Guru Points: 286609 More actions · Answer 5

Pretty sure that nobody is going to be able to help you with such a vague question. We need some ddl and sample data along with the desired output from the sample data. This probably pretty straight forward but I am not at all clear what you are looking for. Please see the link in my signature for best practices on posting a question.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

gsc_dba SSCertifiable Points: 5407 More actions · Answer 6

Edit: As mentioned above - need a little more info to be able to help...

Can you elaborate on the table structures?

gsc_dba

ddnikon SSC-Addicted Points: 489 More actions · Answer 7

Invoice Key 159021 returns 1 invoice row, Invoice expense and Invoice Tax which have 0 rows with invoice key 159021 where invoice key is a foreign key in both those tables.

Invoice key 159078 returns 1 invoice row and has 1 tax row, hence a detail row

I am tasked with creating "header" "H" rows for all invoices, "detail" "D" rows when either invoice expense and invoice tax have an amount > 0 and a "trailer" "T" row with a count of all "D" rows, so if no "D" rows are present for an invoice, I still need to produce the "T" row with a count of zero.

output data from invoice key 159021: H^ is header & T^ is trailer record both start with invoice key

159021H^260101^Zale Corporation^035-200600001-OAO^035-200600001^01/26/2006^200601^CT^United States^Broomfield^30^US$^United States^Broomfield^30^13000.00^^^^^^1

159021T^^^^^^^^^^^^^^^^^^^^0000014

output date from invoice key 159078:

159078H^181529^RBC Financial Group^063-200600013-OAO^063-200600013^02/28/2006^200603^CT^United States^Detroit^64^FRF^United States^Detroit^43^3081.60^^^^^^1

159078D^^^^^^^^^^^^^^^^TAX^^^201.60^^3

159078T^^^^^^^^^^^^^^^^^^^^0000014

Hope this helps!

Sean Lange SSC Guru Points: 286609 More actions · Answer 8

I'm guessing you didn't actually read the article I pointed you to. If you post some ddl, sample data (insert statements) and desired output with a clear explanation you will there are lots of people willing and able to help you.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Please take the time to read the article previously referenced (happens to be the first article I reference below in my signature block). Following those guidelines will get you plenty of help, plus the benefit of tested code in return.

Also, based on your code, if there are no records in some of the child tables you will not get any data. Your code is using inner joins in places where it is apparent that you need to use left outer joins.