asked on

SQL Select

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 UnAssigned
FROM  tblMainHistory v, tblLocationCode h
WHERE h.LOCATIONCODE =* v.LOCATION
GROUP BY RecordDate

Open in new window

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:

recorddate      FLOOR1      FLOOR2
20100405      2        1
20100405      2        1

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! :)

Thanks for any help you can provide.

gsiric

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 UnAssigned
FROM  
(SELECT DISTINCT LOCATION,RECORDDATE FROM tblMainHistory)  v, 
tblLocationCode h
WHERE h.LOCATIONCODE =* v.LOCATION
GROUP BY RecordDate

Open in new window

ASKER CERTIFIED SOLUTION

gsiric

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Alpesh Patel

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

amb1313

You can use a GROUP BY and then a DISTINCT off of that.

ttist25

ASKER

Thanks!