Why does my SQL syntax show null?

Question

I am little perplexed on why my result of this statement shows null in the Project_ID. I took a look at the below post and tried the inner join instead but the result was futile, the same as with the left join. I have only two distinct Project_ID. For some odd reason, the Name is being stated again with the Count added from the previous listings. Why is it doing this and how can I fix this? Please provide your advice.

Why does my SQL query return rows with NULL? It should never return rows with NULL

select dp."Name",count(dp."Name") Count,max(to_char(ft."Hours",'9,999')) "Maximum Hours Worked",
max(ft."Salary"::money) "Maximum Salary",
ft."Project_ID"
from facttable ft
left join alldatainput dp on dp."alldatainputpk" = ft."alldatainputfk"
group by rollup(dp."Name",ft."Project_ID")

Result:

       Name             Count    Maximum Hours Worked  Maximum Salary  Project_ID
Hulk Hogan              157           3,500             $432,995.00     LFC
Hulk Hogan              43            3,499             $550,000.00     PCR
Hulk Hogan              200           3,500             $550,000.00   (null)
Andre the Giant         42            5,300             $870,000.00     PCR
Andre the Giant         42            5,300             $870,000.00   (null)
Bret Hart                5            3,675             $512,000.00     LFC
Bret Hart               10            4,193             $716,510.00     PCR
Bret Hart               15            4,193             $716,510.00   (null)
Winnie the Pooh         561           5,600             $929,654.00     PCR
Winnie the Pooh         561           5,600             $929,654.00   (null)
                        1000          5,600             $929,654.00   (null)

It's from the rollup - It's applying your aggregates over each grouping of Name and Project_Id as a summary. Thus, you get a total count and the max records for the group from hours worked and salary. The final record is a summary of the entire set. — Siyual
– Siyual, Commented Feb 16, 2017 at 16:19
If I remove the rollup, it would not deliver the total for each of the columns for me. What's the most viable way to add a total to all the columns for this statement then? — Jake Wagner
– Jake Wagner, Commented Feb 16, 2017 at 16:20
Unfortunately, I don't know enough about postgres syntax to answer how to filter out the sub-groupings. — Siyual
– Siyual, Commented Feb 16, 2017 at 16:24
No problems @ Siyual. We are all here to learn from each other.... — Jake Wagner
– Jake Wagner, Commented Feb 16, 2017 at 16:25
Your query doesn't produce a column called Project_Id, so that query is not producing the specified results. — Gordon Linoff
– Gordon Linoff, Commented Feb 16, 2017 at 16:50

Clodoaldo Neto · Accepted Answer · 2017-02-16 16:53:03Z

1

Grouping Sets

select
    dp.Name,count(dp."Name") Count,
    max(to_char(ft."Hours",'9,999')) "Maximum Hours Worked",
    max(ft."Salary"::money) "Maximum Salary",
    ft."Project_ID"
from
    facttable ft
    left join
    alldatainput dp on dp."alldatainputpk" = ft."alldatainputfk"
group by grouping sets ((dp."Name",ft."Project_ID"), ())

answered Feb 16, 2017 at 16:53

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why does my SQL syntax show null?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related