Calculating Quartiles Using SQL

Given the following table with values (note that the table containes more products and prices than in the table below):
AveragePrice
ProductNumber
Year
45.7820
2
2015
46.0142
2
2016
59.0133
2
2017
60.1707
2
2018
62.6600
2
2019
I am running the following query:
Which gives the following result:
AveragePrice
Quartile
45.7820
1
46.0142
1
59.0133
2
60.1707
3
62.6600
4
For full context the query in it’s whole looks like this:
But when I manually calculate the quartile the first quartile should be:
45.8981
(average of the first and second row in this particular case) not 46.0142.
I am aware that SQL Server has a built in function that facilitates calculation of quartiles, the NTILE function.


Question:

I am utilizing PostgreSQL and currently have a table containing close to one million entries.

  CREATE TABLE Data(
  id varchar(8) PRIMARY KEY,
  jan INT,
  feb INT,
  mar INT,
  apr INT,
  may INT,
  jun INT,
  jul INT,
  aug INT,
  sep INT, 
  oct INT,
  nov INT,
  dec INT);

And i want to get the
quartil
es

For example:

ID    jan  feb  mar apr may jun jul aug sep oct nov dec
A10    1    2   3    4   5   6   7   8   9  10  11  12

I want to output as:

ID    Q1  Q2  Q3 Q4
A10    3  6   9  12

How can i do that in postgresql?


Solution 1:

Perhaps this could yield the outcome you are seeking.

Select id, 
       jan + feb + mar as Q1,
       apr + may + jun as Q2,
       jul + aug + sep as Q3,
       oct + nov + dec as Q4,
From Data


Solution 2:


Simply combine the columns that comprise the Quarters.

select id
, (case 
     when jan>feb and jan>mar then jan
     when feb>jan and feb>mar then feb
     else mar END
       ) q1
...Similarly for q2,q3,q4
from table 

Frequently Asked Questions

Posted in Sql