I have the following query that was graciously provided here.

SELECT recorddate, sum(case when h.description = 'First Floor' then 1 else 0 end) as Floor1 , sum(case when h.description = 'Second Floor' then 1 else 0 end) as Floor2 , sum(case when h.description = 'Third Floor' then 1 else 0 end) as Floor3 , sum(case when h.description = 'Fourth Floor' then 1 else 0 end) as Floor4 , sum(case when h.description = 'Fifth Floor' then 1 else 0 end) as Floor5 , sum(case when h.description = 'Sixth Floor' then 1 else 0 end) as Floor6 , sum(case when h.description = 'Seventh Floor' then 1 else 0 end) as Floor7 , sum(case when h.description = 'Eighth Floor' then 1 else 0 end) as Floor8 , sum(case when h.description = 'Ninth Floor' then 1 else 0 end) as Floor9 , sum(case when h.description = 'Tenth Floor' then 1 else 0 end) as Floor10 , sum(case when h.description IS null then 1 else 0 end) as UnAssignedFROM tblMainHistory v, tblLocationCode hWHERE h.LOCATIONCODE =* v.LOCATIONGROUP BY RecordDate

In the source table (tblMainHistory) there may be multiple instances of the same record. These are distinguished by a 'RECORDIDNUMBER'. So, somehow I need to COUNT(DISTINCT RECORDIDNUMBER) with that query.

For instance, example data from tblMainHistory might look like this:
In that example the result of the query would be:

The technicolor spreadsheet image was a sincere effort to facilitate your understanding - I hope it didn't confuse you more than help you. My mind works in STRANGE ways! :)

Why you dont first select distinct rows from tblMainHistory like this ?

SELECT recorddate, sum(case when h.description = 'First Floor' then 1 else 0 end) as Floor1 , sum(case when h.description = 'Second Floor' then 1 else 0 end) as Floor2 , sum(case when h.description = 'Third Floor' then 1 else 0 end) as Floor3 , sum(case when h.description = 'Fourth Floor' then 1 else 0 end) as Floor4 , sum(case when h.description = 'Fifth Floor' then 1 else 0 end) as Floor5 , sum(case when h.description = 'Sixth Floor' then 1 else 0 end) as Floor6 , sum(case when h.description = 'Seventh Floor' then 1 else 0 end) as Floor7 , sum(case when h.description = 'Eighth Floor' then 1 else 0 end) as Floor8 , sum(case when h.description = 'Ninth Floor' then 1 else 0 end) as Floor9 , sum(case when h.description = 'Tenth Floor' then 1 else 0 end) as Floor10 , sum(case when h.description IS null then 1 else 0 end) as UnAssignedFROM (SELECT DISTINCT LOCATION,RECORDDATE FROM tblMainHistory) v, tblLocationCode hWHERE h.LOCATIONCODE =* v.LOCATIONGROUP BY RecordDate

SELECT recorddate, sum(case when h.description = 'First Floor' then 1 else 0 end) as Floor1 , sum(case when h.description = 'Second Floor' then 1 else 0 end) as Floor2 , sum(case when h.description = 'Third Floor' then 1 else 0 end) as Floor3 , sum(case when h.description = 'Fourth Floor' then 1 else 0 end) as Floor4 , sum(case when h.description = 'Fifth Floor' then 1 else 0 end) as Floor5 , sum(case when h.description = 'Sixth Floor' then 1 else 0 end) as Floor6 , sum(case when h.description = 'Seventh Floor' then 1 else 0 end) as Floor7 , sum(case when h.description = 'Eighth Floor' then 1 else 0 end) as Floor8 , sum(case when h.description = 'Ninth Floor' then 1 else 0 end) as Floor9 , sum(case when h.description = 'Tenth Floor' then 1 else 0 end) as Floor10 , sum(case when h.description IS null then 1 else 0 end) as UnAssignedFROM (SELECT DISTINCT RECORDIDNUMBER, LOCATION,RECORDDATE FROM tblMainHistory) v, tblLocationCode hWHERE h.LOCATIONCODE =* v.LOCATIONGROUP BY RecordDate

SELECT recorddate, sum(case when h.description = 'First Floor' then 1 else 0 end) as Floor1
, sum(case when h.description = 'Second Floor' then 1 else 0 end) as Floor2
, sum(case when h.description = 'Third Floor' then 1 else 0 end) as Floor3
, sum(case when h.description = 'Fourth Floor' then 1 else 0 end) as Floor4
, sum(case when h.description = 'Fifth Floor' then 1 else 0 end) as Floor5
, sum(case when h.description = 'Sixth Floor' then 1 else 0 end) as Floor6
, sum(case when h.description = 'Seventh Floor' then 1 else 0 end) as Floor7
, sum(case when h.description = 'Eighth Floor' then 1 else 0 end) as Floor8
, sum(case when h.description = 'Ninth Floor' then 1 else 0 end) as Floor9
, sum(case when h.description = 'Tenth Floor' then 1 else 0 end) as Floor10
, sum(case when h.description IS null then 1 else 0 end) as UnAssigned
FROM (Select Distinct Fields From tblMainHistory) v Left Outer Join tblLocationCode h
on v.LOCATION =h.LOCATIONCODE
GROUP BY RecordDate

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…

Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.