1 Replies - 721 Views - Last Post: 02 October 2013 - 12:14 PM Rate Topic: -----

#1 Murgen  Icon User is offline

  • New D.I.C Head
  • member icon

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 06-July 09

Teradata SQL - Creating Groups by fixed percents

Posted 30 September 2013 - 12:54 PM

Hello,

I am trying to create a query that pulls back groupings of the fixed percents. Basically I am creating "Rankings" for the fixed percentages.

For instance,

I have a table built. It has roughly 4,000 rows. It has 20 columns, and I want to pull back something like this:

SELECT
Column1
Column2
Column3
Column4
Column5
Column6
Column7
Column8
Column9
Column10
Column11
Column12
Column13
Column14
Column15
Column16
Column17
Column18
Column19
Column20
TOP 2 PERCENT Column2 AS Super High
TOP 8 PERCENT Column2 AS High
FROM 
DATABASE 1


Any thoughts? I can't seem to think of a way to go about doing this.

Is This A Good Question/Topic? 0
  • +

Replies To: Teradata SQL - Creating Groups by fixed percents

#2 ForcedSterilizationsForAll  Icon User is offline

  • D.I.C Addict

Reputation: 33
  • View blog
  • Posts: 506
  • Joined: 16-July 09

Re: Teradata SQL - Creating Groups by fixed percents

Posted 02 October 2013 - 12:14 PM

So you're trying to flag the top 2 percent as super high and the top 8 percent as high?

Can't you just do a left join to the table itself where you get the top 2 percent and another left join to the table itself where you get the top 8 percent?


(I have not tested the code but it should theoretically work and you may need to bring back some other columns for whatever you need to do to match the table to itself).

SELECT *, CASE WHEN COALESCE(t1_2pct.Column2, 0) <> 0 THEN 'Super High' ELSE '' END AS Top2Pct, CASE WHEN COALESCE(t1_8pct.Column2, 0) <> 0 THEN 'High' ELSE '' END AS Top8Pct
FROM table1 t1
LEFT JOIN
(
   SELECT TOP 2 PERCENT Column1, Column2
   FROM table1
   ORDER BY Column2
) t1_2pct
  ON t1.Column1 = t1_2pct.Column1
LEFT JOIN
(
   SELECT TOP 8 PERCENT Column1, Column2
   FROM table1
   ORDER BY Column2
) t1_8pct
   ON t1.Column1 = t1_8pct.Column1



This post has been edited by ForcedSterilizationsForAll: 02 October 2013 - 12:15 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1