[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Select

Posted on 2011-05-06
5
Medium Priority
?
309 Views
Last Modified: 2012-05-11
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:
 Faux sample data
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.
0
Comment
Question by:ttist25
5 Comments
 
LVL 7

Expert Comment

by:gsiric
ID: 35705875
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

0
 
LVL 7

Accepted Solution

by:
gsiric earned 2000 total points
ID: 35705889
ups forgot recordidnumber in distinct :)


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

Open in new window

0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35705935
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
0
 

Expert Comment

by:amb1313
ID: 35706081
You can use a GROUP BY and then a DISTINCT off of that.
0
 
LVL 1

Author Closing Comment

by:ttist25
ID: 35706718
Thanks!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question