SQL Basics

 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me



Go Back   Tutorialized ForumsDatabasesSQL Basics

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Tutorialized Forums Sponsor:
  #1  
Old October 4th, 2015, 05:33 PM
mountainclimber mountainclimber is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Oct 2015
Posts: 1 mountainclimber User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 m 12 sec
Reputation Power: 0
How to add quartile (percentile) to existing query

I have the following query:

Code:
SELECT  "Cc EV PS" AS factor, 
        "GICS Sector/" & t1.[GICS Sector], 
        #8/14/2015# AS calcdate, 
        (Sum((t1.[Cc EV PS]-z.TheAvg)^4)/Count(t1.[Cc EV PS]))/(Sum((t1.[Cc EV PS]-z.TheAvg)^2)/Count(t1.[Cc EV PS]))^2 AS Kurtosis
FROM tbl_DatedModel_2015_0929_0 AS t1 
   INNER JOIN
            (SELECT t2.[GICS Sector], Avg(t2.[Cc EV PS]) AS TheAvg 
            FROM tbl_DatedModel_2015_0929_0 AS t2 
            GROUP BY t2.[GICS Sector]) AS z ON t1.[GICS Sector] = z.[GICS Sector]
GROUP BY t1.[GICS Sector]
HAVING Count(t1.[Cc EV PS]) > 0;


That I am attempting to add 25Percentile to the SQL above. Here is the SQL for 25Prercentile:

Code:
SELECT TOP 1 0.75*( SELECT Max(tp2.[Cc EV PS]) 
                    FROM tbl_DatedModel_2015_0929_0 AS tp2
                    WHERE tp2.[Cc EV PS] IN 
                        (SELECT TOP 25 PERCENT tp3.[Cc EV PS] 
                        FROM tbl_DatedModel_2015_0929_0 AS tp3 
                        WHERE tp3.[Cc EV PS] Is Not Null 
                        ORDER BY tp3.[Cc EV PS])) + 0.25*
                            (SELECT Min(tp4.[Cc EV PS]) 
                            FROM tbl_DatedModel_2015_0929_0 AS tp4
                            WHERE tp4.[Cc EV PS] IN 
                                (SELECT TOP 75 PERCENT tp5.[Cc EV PS] 
                                FROM tbl_DatedModel_2015_0929_0 AS tp5
                                WHERE tp5.[Cc EV PS] Is Not Null 
                                ORDER BY tp5.[Cc EV PS] DESC)) AS 25Percentile
FROM tbl_DatedModel_2015_0929_0 AS tp1;


In the end the 25Percentile should produce a percentile calculation for subset created in the first query similar to the way Kurtosis is calculated in the first query (i.e. on a subset of the data). The SQL I provided above for 25Percentile just calculates on all of the data.

This has to work in MS Access 2013.

I have a VBA answer that is super slow. I want pure SQL answers only please.

The first query works exactly how I want it to to work, except I need to include a 25Percentile calc.

In case you are confused, the first query is used in this way, but I didn't include all of that because I thought it would just distract:

Code:
SELECT  "Cc EV PS" AS factor, 
        "GICS Sector/" & t1.[GICS Sector], 
        #8/14/2015# AS calcdate, 
        (Sum((t1.[Cc EV PS]-z.TheAvg)^4)/Count(t1.[Cc EV PS]))/(Sum((t1.[Cc EV PS]-z.TheAvg)^2)/Count(t1.[Cc EV PS]))^2 AS Kurtosis
FROM tbl_DatedModel_2015_0929_0 AS t1 
   INNER JOIN
            (SELECT t2.[GICS Sector], Avg(t2.[Cc EV PS]) AS TheAvg 
            FROM tbl_DatedModel_2015_0929_0 AS t2 
            GROUP BY t2.[GICS Sector]) AS z ON t1.[GICS Sector] = z.[GICS Sector]
GROUP BY t1.[GICS Sector]
HAVING Count(t1.[Cc EV PS]) > 0
UNION ALL
SELECT  "USD Market Cap" AS factor, 
        "GICS Sector/" & t1.[GICS Sector], 
        #8/14/2015# AS calcdate, 
        (Sum((t1.[USD Market Cap]-z.TheAvg)^4)/Count(t1.[USD Market Cap]))/(Sum((t1.[USD Market Cap]-z.TheAvg)^2)/Count(t1.[USD Market Cap]))^2 AS Kurtosis
FROM tbl_DatedModel_2015_0929_0 AS t1 
   INNER JOIN
            (SELECT t2.[GICS Sector], Avg(t2.[USD Market Cap]) AS TheAvg 
            FROM tbl_DatedModel_2015_0929_0 AS t2 
            GROUP BY t2.[GICS Sector]) AS z ON t1.[GICS Sector] = z.[GICS Sector]
GROUP BY t1.[GICS Sector]
HAVING Count(t1.[USD Market Cap]) > 0
UNION ALL SELECT    "IU Mkt Cap" AS factor, 
        "GICS Sector/" & t1.[GICS Sector], 
        #8/14/2015# AS calcdate, 
        (Sum((t1.[IU Mkt Cap]-z.TheAvg)^4)/Count(t1.[IU Mkt Cap]))/(Sum((t1.[IU Mkt Cap]-z.TheAvg)^2)/Count(t1.[IU Mkt Cap]))^2 AS Kurtosis
FROM tbl_DatedModel_2015_0929_0 AS t1 
   INNER JOIN
            (SELECT t2.[GICS Sector], Avg(t2.[IU Mkt Cap]) AS TheAvg 
            FROM tbl_DatedModel_2015_0929_0 AS t2 
            GROUP BY t2.[GICS Sector]) AS z ON t1.[GICS Sector] = z.[GICS Sector]
GROUP BY t1.[GICS Sector]
HAVING Count(t1.[IU Mkt Cap]) > 0;


This includes the 25Percentile (1st quartile), but it isn't aggregating on the GICS Sector like Kurtosis is, which produces the same value for all GICS Sectors. It should produce one answer for reach subset (GICS Sector):

Code:
SELECT  "Crescat EV PS" AS factor, 
        "GICS Sector/" & t1.[GICS Sector], 
        #8/14/2015# AS calcdate, 
        (Sum((t1.[Crescat EV PS]-z.TheAvg)^4)/Count(t1.[Crescat EV PS]))/(Sum((t1.[Crescat EV PS]-z.TheAvg)^2)/Count(t1.[Crescat EV PS]))^2 AS Kurtosis,
         0.75*(SELECT Max(tp2.[Crescat EV PS]) 
                FROM tbl_DatedModel_2015_0929_0 AS tp2
                WHERE tp2.[Crescat EV PS] IN 
                    (SELECT TOP 25 PERCENT tp3.[Crescat EV PS] 
                    FROM tbl_DatedModel_2015_0929_0 AS tp3 
                    WHERE tp3.[Crescat EV PS] Is Not Null 
                    ORDER BY tp3.[Crescat EV PS])) + 0.25*
                        (SELECT Min(tp4.[Crescat EV PS]) 
                        FROM tbl_DatedModel_2015_0929_0 AS tp4
                        WHERE tp4.[Crescat EV PS] IN 
                            (SELECT TOP 75 PERCENT tp5.[Crescat EV PS] 
                            FROM tbl_DatedModel_2015_0929_0 AS tp5
                            WHERE tp5.[Crescat EV PS] Is Not Null 
                            ORDER BY tp5.[Crescat EV PS] DESC)) AS 25Percentile
FROM tbl_DatedModel_2015_0929_0 AS t1 
   INNER JOIN
            (SELECT t2.[GICS Sector], Avg(t2.[Crescat EV PS]) AS TheAvg 
            FROM tbl_DatedModel_2015_0929_0 AS t2 
            GROUP BY t2.[GICS Sector]) AS z ON t1.[GICS Sector] = z.[GICS Sector]
GROUP BY t1.[GICS Sector]
HAVING Count(t1.[Crescat EV PS]) > 0

Reply With Quote
Reply

Viewing: Tutorialized ForumsDatabasesSQL Basics > How to add quartile (percentile) to existing query


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 

Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.

© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap