SQL for Aggregate 25 percentile value for subsets in MS Access?

[mountainclimber]

This gets the 25th percentile value of the GM field when field GICS Sector = "Energy":

SELECT
  tblFirst250.[GICS Sector]
  , 0.75*(
    SELECT Max(GM) 
    FROM tblFirst250 
    WHERE tblFirst250.GM IN (
      SELECT TOP 25 PERCENT GM 
      FROM tblFirst250 
      WHERE tblFirst250.[GICS Sector ]= "Energy" AND GM Is Not Null ORDER BY GM)) + 0.25*(
        SELECT Min(GM) 
        FROM tblFirst250 
        WHERE tblFirst250.GM IN (
          SELECT TOP 75 PERCENT GM 
          FROM tblFirst250 
          WHERE tblFirst250.[GICS Sector] = "Energy" AND GM Is Not Null ORDER BY GM DESC)
  ) AS 25Percentile
FROM tblFirst250
WHERE tblFirst250.[GICS Sector] = "Energy"
GROUP BY tblFirst250.[GICS Sector];

...which correctly produces:


GICS Sector, 25Percentile
Energy, -1.2


Now I am trying to get all of the 25th percentiles for each GICS Sector. It would look something like this:


GICS Sector, 25Percentile
Energy, -1.2
Industrials, [some value]
Materials, [some value] ...etc.


Here is one of my 500 attempts, which does not work correctly:

SELECT
  tblFirst250.[GICS Sector]
  , 0.75*(
    SELECT Max(GM) 
    FROM tblFirst250 
    WHERE tblFirst250.GM IN (
      SELECT TOP 25 PERCENT GM 
      FROM tblFirst250 
      WHERE (tblFirst250.[GICS Sector ] = "Energy" OR  "Industrials") AND GM Is Not Null ORDER BY GM)) + 0.25*(
        SELECT Min(GM) 
        FROM tblFirst250 
        WHERE tblFirst250.GM IN (
          SELECT TOP 75 PERCENT GM 
          FROM tblFirst250 
          WHERE (tblFirst250.[GICS Sector ] = "Energy" OR  "Industrials") AND GM Is Not Null ORDER BY GM DESC)
  ) AS 25Percentile
FROM tblFirst250
WHERE tblFirst250.[GICS Sector] = "Energy" OR "Industrials"
GROUP BY tblFirst250.[GICS Sector];

...the above results in the same value for all GICS Sectors. I want a different 25th percentile value for each GICS Sector.

[lfpm062010]

I think the problem is here.

WHERE tblFirst250.[GICS Sector] = "Energy" OR "Industrials"

Change to

WHERE tblFirst250.[GICS Sector] = "Energy" OR tblFirst250.[GICS Sector] = "Industrials"

Alternatively, you can use the IN

WHERE tblFirst250.[GICS Sector] IN ("Energy", "Industrials")

[mountainclimber]

I think the problem is here.

WHERE tblFirst250.[GICS Sector] = "Energy" OR "Industrials"

Change to

WHERE tblFirst250.[GICS Sector] = "Energy" OR tblFirst250.[GICS Sector] = "Industrials"

Alternatively, you can use the IN

WHERE tblFirst250.[GICS Sector] IN ("Energy", "Industrials")

Your first suggestion, which I have tried already:

SELECT
tblFirst250.[GICS Sector]
, 0.75*(
SELECT Max(GM)
FROM tblFirst250
WHERE tblFirst250.GM IN (
SELECT TOP 25 PERCENT GM
FROM tblFirst250
WHERE tblFirst250.[GICS Sector] = "Energy" OR tblFirst250.[GICS Sector] = "Industrials" AND GM Is Not Null ORDER BY GM)) + 0.25*(
SELECT Min(GM)
FROM tblFirst250
WHERE tblFirst250.GM IN (
SELECT TOP 75 PERCENT GM
FROM tblFirst250
WHERE tblFirst250.[GICS Sector] = "Energy" OR tblFirst250.[GICS Sector] = "Industrials" AND GM Is Not Null ORDER BY GM DESC)
) AS 25Percentile
FROM tblFirst250
WHERE tblFirst250.[GICS Sector] = "Energy" OR "Industrials"
GROUP BY tblFirst250.[GICS Sector];

....produces no values like this:

GICS Sector 25Percentile


Consumer Discretionary
Consumer Staples
Energy
Financials
Health Care
Industrials
Information Technology
Materials
Telecommunication Services
Utilities

Your second suggestion, which I have also tried:

SELECT
tblFirst250.[GICS Sector]
, 0.75*(
SELECT Max(GM)
FROM tblFirst250
WHERE tblFirst250.GM IN (
SELECT TOP 25 PERCENT GM
FROM tblFirst250
WHERE tblFirst250.[GICS Sector] IN ("Energy", "Industrials") AND GM Is Not Null ORDER BY GM)) + 0.25*(
SELECT Min(GM)
FROM tblFirst250
WHERE tblFirst250.GM IN (
SELECT TOP 75 PERCENT GM
FROM tblFirst250
WHERE tblFirst250.[GICS Sector] IN ("Energy", "Industrials") AND GM Is Not Null ORDER BY GM DESC)
) AS 25Percentile
FROM tblFirst250
WHERE tblFirst250.[GICS Sector] = "Energy" OR "Industrials"
GROUP BY tblFirst250.[GICS Sector];


...produces the same value for every sector:

GICS Sector 25Percentile


Consumer Discretionary -1.28964344253389
Consumer Staples -1.28964344253389
Energy -1.28964344253389
Financials -1.28964344253389
Health Care -1.28964344253389
Industrials -1.28964344253389
Information Technology -1.28964344253389
Materials -1.28964344253389
Telecommunication Services -1.28964344253389
Utilities -1.28964344253389


I'm looking to get a separate 25th percentile value for each sector, independent of the other sectors.

[lfpm062010]

There are 3 places need to be changed.
It looks like you only changed 2 of the 3 places.

If you can upload a sample database, it will be a lot quicker to debug and fix your issue. Of course, remove anything private, confidential, secret, ... etc before upload the database.

[mountainclimber]

There are 3 places need to be changed.
It looks like you only changed 2 of the 3 places.

If you can upload a sample database, it will be a lot quicker to debug and fix your issue. Of course, remove anything private, confidential, secret, ... etc before upload the database.

I see now that my original question was correct, but the SQL I pasted in may have been misleading. Let me clarify:

I am trying to get ALL of the GICS Sectors, not just energy and industrials.

I'm looking for a list like this with separate/different 25th percentile values for each :

GICS Sector	25Percentile
Consumer Discretionary	[value 1]
Consumer Staples	[value 2]
Energy	[value 3]
Financials	[value 4]
Health Care	[value 5]
Industrials	[value 6]
Information Technology	[value 7]
Materials	[value 8]

In other words, I'm looking for SQL more like this, which is more general:

SELECT tbl.[GICS Sector], 0.75*(
SELECT Max(GM) 
FROM tbl 
WHERE tbl.GM IN (
SELECT TOP 25 PERCENT GM 
FROM tbl 
WHERE tbl.[GICS Sector] = tbl.[GICS Sector] AND GM Is Not Null ORDER BY GM)) + 0.25*(
SELECT Min(GM) 
FROM tbl 
WHERE tbl.GM IN (
SELECT TOP 75 PERCENT GM 
FROM tbl 
WHERE tbl.[GICS Sector] = tbl.[GICS Sector] AND GM Is Not Null ORDER BY GM DESC)
) AS 25Percentile
FROM tbl
WHERE tbl.[GICS Sector] = tbl.[GICS Sector]
GROUP BY tbl.[GICS Sector];

...of course that query doesn't work correctly. It produces the same value for every sector.

I uploaded a sample of my data. It may not have all of the sectors in there, but you get the point. Database3.accdb

Thanks!

[lfpm062010]

Just need to clarify something.

You want the top 25% from each [GICS Sector].

For example,

GICS Sector	GM
Consumer Discretionary
Consumer Discretionary
Consumer Discretionary
Consumer Discretionary
Consumer Discretionary
Consumer Discretionary
Consumer Discretionary	1.3628
Consumer Discretionary
Consumer Discretionary	1.4856
Consumer Discretionary
Consumer Discretionary	1.7543
Consumer Discretionary
Consumer Discretionary
Consumer Discretionary	1.4117
Consumer Discretionary

Should return
Consumer Discretionary: 1.3628 (Not including NULL Value, but do count 0).

Does this sound right to you?

[mountainclimber]

Just need to clarify something.

You want the top 25% from each [GICS Sector].

For example,

GICS Sector	GM
Consumer Discretionary
Consumer Discretionary
Consumer Discretionary
Consumer Discretionary
Consumer Discretionary
Consumer Discretionary
Consumer Discretionary	1.3628
Consumer Discretionary
Consumer Discretionary	1.4856
Consumer Discretionary
Consumer Discretionary	1.7543
Consumer Discretionary
Consumer Discretionary
Consumer Discretionary	1.4117
Consumer Discretionary

Should return
Consumer Discretionary: 1.3628 (Not including NULL Value, but do count 0).

Does this sound right to you?

Consumer Discretionary would be 1.37505 according to Excel, which is how we validate: =QUARTILE.EXC(MyRange,1) ....or .... =PERCENTILE.EXC(MyRange,0.25) Both of those return my value in Excel using the same data. I did some checking and the difference in values isn't due to rounding on the raw data. In my world (finance) the difference is material.

[lfpm062010]

Try this, see if this is correct. Database3.accdb

The basic idea is to build a temp table with all the top 25% from each [GICS Sector] and sum the result from the temp table.

[mountainclimber]

Try this, see if this is correct. Database3.accdb

The basic idea is to build a temp table with all the top 25% from each [GICS Sector] and sum the result from the temp table.

I am still looking at this, but the results seem to be spot on! Thank you so much. If I have any questions I will follow up.

Can someone tell me how to mark this as solved? Thanks.

[lfpm062010]

I have never done it. But, I found this thread. https://www.accessforums.net/forum-s...lved-1828.html

[mountainclimber]

I needed to be signed in first. Oops. Thanks again Ifpm062010.

[mountainclimber]

Try this, see if this is correct. Database3.accdb

The basic idea is to build a temp table with all the top 25% from each [GICS Sector] and sum the result from the temp table.

Actually this is not correct. The 25th percentile is actually 1.375 (as mentioned above), not 1.36. To get 1.36 I am running the Run Me under Macros. Am I doing something wrong?

[lfpm062010]

Ok, based on the data. The GICS Sector has 4 records contain data (1.3628, 1.4856, 1.7543, 1.4117). So, the top 25% (1/4) will only select the 1.3628 and not the others. So, I don't get what you think is 1.375. Unless you have more in the table that in the database.

[mountainclimber]

Ok, based on the data. The GICS Sector has 4 records contain data (1.3628, 1.4856, 1.7543, 1.4117). So, the top 25% (1/4) will only select the 1.3628 and not the others. So, I don't get what you think is 1.375. Unless you have more in the table that in the database.

See the bottom of my post #7. Put those 4 values in A1:A4 in Excel and use one of these formulas to validate the number: =QUARTILE.EXC(A1:A4,1) or =PERCENTILE.EXC(A1:A4,0.25)

They both produce a return value of 1.375025. Which is the true 1st quartile or 25th percentile value. Subtle difference, but significant in my world (hedge funds). We have to use Excel to validate.

[lfpm062010]

It looks like there is no Qualtile function in access. But this article seems to be able to call Qualtile function in access using Access.
http://www.mrexcel.com/forum/microso...-function.html

If I can get it working, I will pass it along.