Data Seperation Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Data Seperation Query

Author

Topic

mihirvb84
Starting Member

11 Posts

Posted - 2011-08-02 : 08:53:42

Hi All,

I have following data in to a table.

Bucket Filenumber Redpage
4088185 140696 1
4088185 205184 1
4088185 262377 0
4088185 262385 0
4088185 262412 1
4088185 449513 1
4088185 463015 1

I want to make a query which gives me a result like number of files having redpage value one and number of files having redpage value zero. This result should be in two different columns as shown below.

Bucket #FilewithRedpage #FilewithoutRedpage
4088185 5 2

---------------
Mihir Borde

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-02 : 09:03:11

Not sure of your table design, so I set up a table variable to pull the results. This returns your expected results:

declare @Table table (bucket int, filenumber int, redpage int)
insert into @Table (bucket, filenumber, redpage)
values

(4088185,140696,1),
(4088185,205184,1),
(4088185,262377,0),
(4088185,262385,0),
(4088185,262412,1),
(4088185,449513,1),
(4088185,463015,1)

select bucket,
SUM(case when redpage=1 then 1 else 0 end) as #FilewithRedpage,
SUM(case when redpage=0 then 1 else 0 end) as #FileWithoutRedPage

from @Table
group by bucket

mihirvb84
Starting Member

11 Posts

Posted - 2011-08-02 : 09:19:41

Great, that works. Can we get two more columns within the same result which show percentage of "FileswithRedpage" and "FileswithoutRedpage"?

---------------
Mihir Borde

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-02 : 09:31:36

Yeah, all you'd need to do is do calculations on the columns like this:

declare @Table table (bucket int, filenumber int, redpage int)
insert into @Table (bucket, filenumber, redpage)
values

(4088185,140696,1),
(4088185,205184,1),
(4088185,262377,0),
(4088185,262385,0),
(4088185,262412,1),
(4088185,449513,1),
(4088185,463015,1)

select bucket,
SUM(case when redpage=1 then 1 else 0 end) as #FilewithRedpage,
SUM(case when redpage=0 then 1 else 0 end) as #FileWithoutRedPage,
SUM(case when redpage=1 then 1 else 0 end)*100.0 /COUNT(*) as '% With Red Page',
SUM(case when redpage=0 then 1 else 0 end)*100.0 /COUNT(*) as '% Without Red Page'

from @Table
group by bucket

You have to be careful of divide by zero errors (use coalesce or nullif functions if that will be a problem)

mihirvb84
Starting Member

11 Posts

Posted - 2011-08-02 : 09:42:32

Awesome man!

---------------
Mihir Borde

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-02 : 21:59:46

:) Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources