CODEWITHSUNDEEP
sqlsql-servergroup-bycase
DontFretBrett
DontFretBrett

Reputation: 1165

SQL Server query with CASE and GROUP BY, field invalid?

Can't figure out what I'm doing wrong here. It says field HasNoteDate is invalid. I just want it to return 1 if there's a date, 0 if there's not and I wanted it grouped so I can get the counts and the sum of loan amounts. I've looked at the stackoverflow answers for grouping by a case and none of them are solving it for me. Thanks!

SELECT    dbo.BDONAMES.UserID, dbo.BRKRMAST.Tier, dbo.BDONAMES.REGION, COUNT(*) AS TierRegionCount, 
                      SUM(dbo.LOAN.LOAN_AMT) AS LoanAmtSum, dbo.LOAN.DEAL_STATUS, CASE WHEN dbo.WORKFLOW.NOTE_DATE IS NULL 
                      THEN 0 ELSE 1 END AS HasNoteDate
FROM         dbo.BRKRMAST INNER JOIN
                      dbo.BRKRREF ON dbo.BRKRMAST.BRKRMASTID = dbo.BRKRREF.BRKRMASTID INNER JOIN
                      dbo.LOAN ON dbo.BRKRREF.LoanId = dbo.LOAN.LoanId INNER JOIN
                      dbo.WORKFLOW ON dbo.LOAN.LoanId = dbo.WORKFLOW.LoanId LEFT OUTER JOIN
                      dbo.BDONAMES ON dbo.BRKRMAST.BDONAMESID = dbo.BDONAMES.BDONAMESID
WHERE     (LEN(dbo.BDONAMES.UserID) > 0) AND (dbo.BRKRMAST.Tier > 0) AND (LEN(dbo.BDONAMES.REGION) > 0)
GROUP BY dbo.BDONAMES.UserID, dbo.BRKRMAST.Tier, dbo.BDONAMES.REGION, dbo.LOAN.LOAN_AMT, dbo.LOAN.DEAL_STATUS, HasNoteDate
ORDER BY dbo.BDONAMES.UserID, dbo.BRKRMAST.Tier, dbo.BDONAMES.REGION

Upvotes: 1

Views: 328

Answers (2)

M.Ali
M.Ali

Reputation: 69554

SELECT    dbo.BDONAMES.UserID, dbo.BRKRMAST.Tier, dbo.BDONAMES.REGION, COUNT(*) AS TierRegionCount, 
                      SUM(dbo.LOAN.LOAN_AMT) AS LoanAmtSum, dbo.LOAN.DEAL_STATUS, CASE WHEN dbo.WORKFLOW.NOTE_DATE IS NULL 
                      THEN 0 ELSE 1 END AS HasNoteDate
FROM         dbo.BRKRMAST INNER JOIN
                      dbo.BRKRREF ON dbo.BRKRMAST.BRKRMASTID = dbo.BRKRREF.BRKRMASTID INNER JOIN
                      dbo.LOAN ON dbo.BRKRREF.LoanId = dbo.LOAN.LoanId INNER JOIN
                      dbo.WORKFLOW ON dbo.LOAN.LoanId = dbo.WORKFLOW.LoanId LEFT OUTER JOIN
                      dbo.BDONAMES ON dbo.BRKRMAST.BDONAMESID = dbo.BDONAMES.BDONAMESID
WHERE     (LEN(dbo.BDONAMES.UserID) > 0) AND (dbo.BRKRMAST.Tier > 0) AND (LEN(dbo.BDONAMES.REGION) > 0)
GROUP BY dbo.BDONAMES.UserID, dbo.BRKRMAST.Tier, dbo.BDONAMES.REGION, dbo.LOAN.DEAL_STATUS, CASE WHEN dbo.WORKFLOW.NOTE_DATE IS NULL 
                                                                                                        THEN 0 ELSE 1 END
ORDER BY dbo.BDONAMES.UserID, dbo.BRKRMAST.Tier, dbo.BDONAMES.REGION

Use columns in your group by exactly the way you have defined in you select Statement, otherwise SQL server tries to GROUP it with the fields that it cannot find in SELECT statement and throws an error.

Upvotes: 1

Iłya Bursov
Iłya Bursov

Reputation: 24156

logic of SQL query execution is not straightforward as common programming languages

here are sample execution stages for your query:

  1. perform join on tables in FROM CLAUSE
  2. perform GROUP BY - this will cause error, as HasNoteDate is not defined for this moment
  3. perform ORDER BY
  4. perform SELECT - only during this stage column HasNoteDate is defined

so, you cannot use undefined column HasNoteDate in GROUP BY, you have to replace it with your statement (CASE WHEN dbo.WORKFLOW.NOTE_DATE IS NULL THEN 0 ELSE 1 END), then sql server will be able to GROUP

Upvotes: 2

Related Questions