sql,ms-access,ms-access-2013 , Quartile/Percentile in MS Access via SQL with a GROUP BY when some values can be NULL

## Question:

Tag: sql,ms-access,ms-access-2013

I am looking to calculate a percentile of a subgroup for a field that can be NULL. Field IU is either 1 or Null. Specifically:

``````*my table: tblFirst250
*group by: IU = 1 (which is Nullable)
*percentile of: GM (which is Nullable)
``````

I am starting with the following (but I am open to better ways of doing this):

``````select T.groupField, 0.75*(select max(myField) from myTable where
myTable.myField in (select top 25 percent myField from myTable where
myTable.groupField = T.groupField order by myField)) + 0.25*(select
min(myField) from myTable where myTable.myField in (select top 75 percent
myField from myTable where myTable.groupField = T.groupField order by
myField desc)) AS 25Percentile from myTable AS T group by T.groupField
``````

Taken directly from here: http://blogannath.blogspot.com/2010/03/microsoft-access-tips-tricks-statistics.html#ixzz3dEf9ZJSq

So far I have this:

``````SELECT T.IU, 0.75*(SELECT Max(GM) FROM tblFirst250 WHERE tblFirst250.GM IN
(SELECT TOP 25 PERCENT GM FROM tblFirst250
WHERE tblFirst250.IU = 1 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.IU = 1 AND GM Is Not Null ORDER BY GM desc)) AS 25Percentile
FROM tblFirst250 AS T
GROUP BY T.IU;
``````

Which yields: IU , 1 ;25Percentile -0.706278906030414, -0.706278906030414

...which looks like the quartile of everything assigned to everything.

Issues/questions/requests/notes:

1. I would like the following where the value is the quartile 25 where IU = 1: IU 1; 25Percentile -0.706278906030414
2. The query is pretty slow.
3. It's the sub queries that are tripping me up I think.

Answer:

Just a missing WHERE clause near the bottom:

``````SELECT T.IU, 0.75*(SELECT Max(GM) FROM tblFirst250
WHERE tblFirst250.GM IN (SELECT TOP 25 PERCENT GM FROM tblFirst250 WHERE tblFirst250.IU = 1 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.IU = 1 AND GM Is Not Null ORDER BY GM DESC))  AS 25Percentile
FROM tblFirst250 AS T
WHERE T.IU = 1
GROUP BY T.IU;
``````

# Related:

## Pull information from SQL database and getting login errors

php,sql,database
I am creating a very small, simple CRM for a company, they require the function to be able to view the last 25 orders via the dashboard. The orders are added via a Order-add form within the CRM. When adding the following code to the CRM I get an error:...

## Retrieve Values As Column

mysql,sql
I've the following table in my PHPMYADMIN The desired output is headline                              impressions              clicks Buy A new Iphone                 ...

## I need help setting the RecordSource of a Report within a VBA Function

vba,ms-access,access-vba
Ok, so I've been looking around here for a few days (and a few other sites) and while I have modified my code a good bit to find a solution, it still doesn't work. I have four source queries, and I'd like to set the record source of my report...

## Default the year based on month value

sql,sql-server
I have a query to display the year value from a table and default the current year. select distinct year(startdate) as syear, year(startdate) as eyear, 1 as sOrder from [TTS].[dbo].[Class] where year(startdate) <> year(getdate()) union all select year(getdate()) as syear, year(getdate()) as eyear, 0 as sOrder order by sOrder asc,...

## oracle sql error Case When Then Else

sql,oracle,oracle11g
SELECT * FROM FirstTable WHERE RowProcessed = 'N' AND ( CASE WHEN EXISTS(SELECT top 1 FROM SecondTable) THEN 1 ELSE EXISTS( SELECT SecondTable.RowProcessed FROM SecondTable WHERE FirstTable.Key = SecondTable.Key AND SecondTable.RowProcessed = 'Y' ) END ) AND OtherConditions Case When then else in where clause. Not sure about the syntax....

## How to join result of a Group by Query to another table

sql,tsql
I have the following Group By SQL: SELECT s.Login_Name, COUNT(s.s1CIDNumber) FROM [dbSuppHousing].[dbo].[tblSurvey] s group by s.Login_Name I want to know how I can join this result set to another table(tblUsers) to add user_id to the result set (tblSurvey and tblUsers have 1-1 relationship on Login_Name) I tried the following: Select...

## Entity Framework code-first: querying a view with no primary key

sql,oracle,entity-framework,view,ef-code-first
Our customer has given the access to views in which there is no primary key is defined. I know Entity Framework needs a primary key for table to identify. But for views not having primary key is it still possible to query. I try to find but always Entity Framework...

## Fastest way to add a grouping column which divides the result per 4 rows

sql,sql-server,tsql,sql-server-2012
If i have a resultset like this for example (just a list of numbers) : 1,2,3,4,5,6,7,8,9,10,11 and I would like to add a grouping column so i can group them per 4 like this : 1,1,1,1,2,2,2,2,3,3,3 (The last one in this examle does not have a forth element, so that...

## SQL Group By multiple categories

php,mysql,sql,mysqli
This is the query I'm using to count how many events are booked for each category of bookings. SELECT Categories.name, count(case when locations.name ='loc 1' then 1 end) as Location1, count(case when locations.name ='loc 2' then 1 end) as Location2, count(case when locations.name ='loc 3' then 1 end) as Location3,...

## Take thousand value in SQL

sql,sql-server
I have values like below I need to take only the thousand value in sql. 38,635.123 90,232.89 123,456.47888 I need to take result as below. 635 232 456...

## left join table, find both null and match value

sql,sql-server,join
I have two 2 table t1(years int, numOfppl int), t2(years int, numOfppl int). t1 contains years between 2001 and 2010 , t2 contains years between 2003-2005 and 2007-2010 I want to query a result like that t1.years t1.numOfppl t2.yeras t2.numOfppl 2001 7 null null 2002 6 null null 2003 4...

## for-loop add columns using SQL in MS Access

sql,ms-access,table,for-loop,iteration
I am trying to add n columns to a table, like in this example of code where n = 10: Sub toto() Dim db As Database, i As Integer Set db = CurrentDb() For i = 1 To i = 10 db.Execute " ALTER TABLE time_series " _ & "ADD...

## SQL only one cell from column can have X value

mysql,sql
Is there a way to ensure, when you design a table - SQL, MySQL -, that only one cell from column X is true, the rest being automatically false? When you change the value of a different cell to true, all other cells should automatically become false. A radio button...

## SQL: overcoming no ORDER BY in nested query

sql,sqlite
I have a table t as follows: CREATE TABLE t( id INTEGER PRIMARY KEY AUTOINCREMENT, date TEXT, a TEXT, b TEXT ); Sample Data 1|2015-06-15|a1 15|b1 15 2|2015-06-15|a2 15|b2 15 3|2015-06-16|a1 16|b1 16 4|2015-06-16|a2 16|b2 16 5|2015-06-17|a1 17|b1 17 6|2015-06-17|a2 17|b2 17 I want to select all values of a...

## SQL Repeated Condition in Two Tables

sql,variables,coding-style,condition
I am a beginner to SQL, and I am having trouble getting rid of repeated code. I have a condition "AccountId=20" that I use twice in a SQL query. The SQL looks something like: DELETE FROM Table_A WHERE FunctionId IN (Select FunctionId FROM Table_B WHERE AccountId=20); DELETE FROM Table_B WHERE...

## Can someone explain to me how this statement is an exclude?

sql,sql-server,tsql
Can someone tell me how this is an exclude? Assuming that tableID is auto generated and in columnY there can be value of 0 or 1. This statement should exclude everything were columnY has a value of 1. SELECT * FROM [table].[dbo].[one] AS t1 LEFT JOIN [table].[dbo].[one] AS t2 ON...

## Select all none null values from all columns in a table

mysql,sql
I am having an issue going through column by column and getting a count of all the non-null values in each column. I think issue is how I am getting the column names. In the last select statement where I am trying to select all of the values in each...

## Matplotlib: Plot the result of an SQL query

python,sql,matplotlib,plot
from sqlalchemy import create_engine import _mssql from matplotlib import pyplot as plt engine = create_engine('mssql+pymssql://**:[email protected]:1433/AffectV_Test') connection = engine.connect() result = connection.execute('SELECT Campaign_id, SUM(Count) AS Total_Count FROM Impressions GROUP BY Campaign_id') for row in result: print row connection.close() The above code generates an array: (54ca686d0189607081dbda85', 4174469) (551c21150189601fb08b6b64', 182) (552391ee0189601fb08b6b73', 237304) (5469f3ec0189606b1b25bcc0',...

## Query how often an event occurred at a given time

mysql,sql
[Aim] We would like to find out how often an event "A" ocurred before time "X". More concretely, given the dataset below we want to find out the count of the prior purchases. [Context] DMBS: MySQL 5.6 We have following dataset: user | date 1 | 2015-06-01 17:00:00 2 |...

## mysql_real_escape_string creates \ in server only not in local

php,sql
When I use mysql_real_escape_string in my localhost and I output the result of html in a table I have no problem. But when I use it on my server it outputs even the \ This is how I use it: \$_GETVARS['txtEmpNum'] = mysql_real_escape_string(\$_GETVARS['txtEmpNum']); \$_GETVARS['txtLName'] = mysql_real_escape_string(\$_GETVARS['txtLName']); \$_GETVARS['txtFName'] = mysql_real_escape_string(\$_GETVARS['txtFName']); \$varSQL...

## How to delete only the table relationed

sql,laravel,migration
I know how to create the migrations with laravel 5.1 and I have the following code public function up() { Schema::create('articulos', function (Blueprint \$table) { \$table->increments('id'); \$table->string('title'); \$table->string('content'); \$table->integer('categoria_id')->unsigned(); \$table->integer('creador_id')->unsigned(); \$table->string('slug', 32); \$table->timestamps(); }); Schema::table('articulos', function(\$table) { \$table->foreign('categoria_id')->references('id')->on('categorias');...

## Convert AWK command to sqlite query

sql,awk,sqlite3
I have to parse very big (abt 40Gb) text files(logs) very often. Usually AWK/grep is enough for my needs, but logs are growing and now I'm curious whether sqlite3 will allow me to do the same things more efficiently. I chosen sqlite for my tests as it installed out-of-the-box (SQLite...

## How do I find records in one table that do exist in another but based on a date?

mysql,sql
I want to find all the dealers who haven't had an order in 2015 yet. I know this query doesn't work but I thought it might be helpful to understand what I want to do. In this example I want to get just "Bob" as a result. He is the...

## Is there a better way to write this query involving a self select?

sql,postgresql,join,aggregate-functions
I am looking for a "better" way to perform a query in which I want to show a single player who he has played previously and the associated win-loss record for each such opponent. Here are the tables involved stripped down to essentials: create table player (player_id int, username text);...

## Title search in SQL With replacement of noice words [on hold]

sql,sql-server,sql-server-2008
I have Two tables first us IMG_detail S.NO Title 1 women holding stack of gifts 2 Rear view of a man playing golf 3 Women holding gifts 4 Close-up of a golf ball on a tee 5 Businessman reading a newspaper and smiling and Second is tbl_NoiceWords SN Key 1...

## Merging two tables into new table by ID and date

sql,sql-server,phpmyadmin
Let' say I have two tables: table 1: col a | col b | col c 1 | 62215 | 21 1 | 62015 | 22 2 | 62215 | 23 2 | 51315 | 24 and table 2: col a | col b| col f 1 | 62015| z...

## Add 1 to datediff result if time pass 14:30 or 2:30 PM

sql,ms-access,ms-access-2007
Here is my SQL query which returns number of days by subtracting current date from specified date and returns exact as i need, but in addition i want to add 1 to result if current time passes 14:30 or 2:30. My query SELECT reservations.customerid, DateDiff("d",reservations.checkin_date,Now()) AS Due_nights FROM reservations Am...

## Is it possible to index views in Apache Solr

sql,view,solr
Let me first give you an example. I have two tables -table1 and table2. table1 has a field id_table2, which is a foreign key and references one of the fields in table2. So, when I want to scan table1, I make a query like: SELECT t1.attr_1_, t1.attr_2_, t2.attr_3_ FROM table1...

## Select count Columns group by No

sql,sql-server
Emp_No Emp_Shift Emp_Date 500 AL 1/5/2015 600 S 2/5/2015 600 H 3/5/2015 500 S 4/5/2015 500 AL 5/5/2015 600 AL 6/5/2015 I need help on this issue , HOW TO RETURN count in 3 Columns >> EX: Emp_No Count Al Count S Count H 500 2 1 0 600 1...

## T-SQL Ordering a Recursive Query - Parent/Child Structure

sql,tsql,recursion,order,hierarchy
I am trying (and failing) to correctly order my recursive CTE. My table consists of a parent-child structure where one task can relate to another on a variety of different levels. For example I could create a task (this is the parent), then create a sub-task from this and then...

## Retrieve data from one table and insert into another table

sql,asp.net,sql-server
I am trying to retrieve data from one table and then insert it into another table. This is a a sample of the first table in which there is the following data. tb1 is the table which consists of data. The two columns Manager and TeamLeader basically means for example...

## MySQL: Select several rows based on several keys on a given column

mysql,sql,database
I have this mysql table structure: ------------------------------------ | item_id | meta_key | meta_value | ------------------------------------ 159 category Bungalow 159 location Lagos 159 price 45000 160 category Bungalow 160 location Abuja 160 price 53500 ... 350 category Bungalow 350 location Lagos 350 price 32000 What I'd like to do is select...

## How can I create missing date records for each employee based off a limited calendar?

mysql,sql,tsql
I have two tables, one an abbreviated calendar: create table dbo.calendar ( [date] datetime ) insert into dbo.calendar values ('20150101 00:00:00 AM') -- 1/1/15 insert into dbo.calendar values ('20150102 00:00:00 AM') -- 1/2/15 insert into dbo.calendar values ('20150103 00:00:00 AM') -- 1/3/15 insert into dbo.calendar values ('20150104 00:00:00 AM') --...

## Inner Join 3 tables pl/sql

mysql,sql
Basically i want to inner join 3 tables and the case is understood by the query itself as follows. Tables: A has 2 columns column 1 and column 2 B has 2 columns column 3 and column 4 C has 3 columns column 5,column 6 and column 7 Query: select...

## Using Sum in If in Mysql

mysql,sql,select,sum
I want to select the sum of values of my_field. The unit of my_field is in seconds. I don't want to exceed 24 hours, so when it exceeds 24 hours, I want it to select 24*60*60 = 86400. SELECT IF(SUM(my_field) > 86400, 86400, SUM(my_field)) ... This solution doesn't seem to...

## SQL Multiple LIKE Statements

sql,sql-server,tsql,variables,like
I'm currently working on a report that shows me all post codes covered by our sales team. Each team covers over 100 post codes. What i would like to do is create a report that brings back the clients within the post code. Currently my code looks like this. SELECT...

## How to select next row after select in SQL Server?

sql,sql-server
I have this issue, I need your help: EmpNo Shift_Date Shift1 shift2 stamp_Date stamp_Time stamp_Type 426 2015-04-12 A 2015-04-12 10:09:00.000 I 426 2015-04-15 B C 2015-04-15 23:46:00.000 I 426 2015-04-15 B C 2015-04-15 23:45:00.000 O 426 2015-04-16 OF 2015-04-16 07:02:00.000 O 426 2015-04-17 A 2015-04-17 07:34:00.000 I 426 2015-04-18 A...

## Purging Database - Count purged/not-purged tables

mysql,sql,sql-server,database,stored-procedures
So I am working with a database where I will be purging various tables that contain rows that are older than 30days. I have fairly limited knowledge of SQL and wanted to know if there was a certain way to select the row that will be purged and the rows...

## Looping distinct values from one table through another without a join

sql,sql-server,tsql,while-loop
I know looping is not ideal in SQL, but I couldn't think of another way of doing this. I want each distinct row from this Table 1 to have each distinct date and hour produced on Table 2. In other words, Table 2 has the dates between 05/01/2014 through 04/30/2015,...

## timestamp SQL to Excel

php,mysql,sql,excel
If this is a duplicate, please let me know, I haven't found anything. I have written a php file that can read content from a database table and write it into a excel .xls file. Everything works fine except by that timestamps. In my generated .xls file every timestamp is...

## The column name “FirstName” specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument

sql,sql-server,sql-server-2008
I am getting the following error message when I am trying to do replace null to zero. The column name "jan" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument. Query below: select * from(select isnull(jan,0),isnull(feb,0),sum(data) as amount )as p pivot( sum(amount) for month...

## Improving work with SQL DataTime

sql,sql-server,database,tsql
I have a business in my Date. I need change date below for each date before 25 of each month I want get 25 of month before and for each date after 25 of each month I want get 25 of the same month. Example : for 2015/06/24 (or 2015/06/06)...

## Get unique row by single column where duplicates exist

sql,sql-server
I have the following DB table called messages with columns: thread_id, sender_id, receiver_id, date Example: +-----------+------------+-------------+-------------+ | thread_id | sender_id | receiver_id | date | +----------------------+-----------+-----------------+ | 1 | 1 | 2 | 11/06/2015 | | 1 | 2 | 1 | 14/06/2015 | | 1 | 1 | 2...

## Foreign key in C#

c#,sql,sql-server,database
I have two tables, A and B, in a dataset in SQL Server; I have created a connection to the dataset in a c# project in visual studio. How can I create a foreign key ( A is the parent) between my two tables ? I want to create the...

## Recursive Lag Column Calculation in SQL

sql,sql-server,recursion
I am trying to write a procedure that inserts calculated table data into another table. The problem I have is that I need each row's calculated column to be influenced by the result of the previous row's calculated column. I tried to lag the calculation itself but this does not...

## SQL varchar variable inserts question mark

sql,sql-server-2012
I've a database value that when inserted into a SQL variable, shows with question mark at the end !! can't find a reason?! declare @A varchar(50) = 'R2300529‏' select @A Results: R2300529? any explanation? i'm using SQL server 2012....

## SQL Customized search with special characters

sql,sql-server,sql-server-2008
I am creating a key-wording module where I want to search data using the comma separated words.And the search is categorized into comma , and minus -. I know a relational database engine is designed from the principle that a cell holds a single value and obeying to this rule...

## Select Statement on Two different views

sql
I couldn't find the answer to my specific question anywhere. Is it possible to select from two different views? For example my code looks something like this right. select view1.col1, view1.col2, view1.col3 from dbo.view1 inner join ~~~~~ inner join ~~~~~ but I want to include a column from a different...