QUARTER() Function in MySQL
QUARTER() function in MySQL is used to return the quarter of the year for a given date value. It returns a number from 1 to 4.
Syntax :
QUARTER(date)
Parameter : The function accepts only one parameter
- date : The date or DateTime from which we want to extract the quarter.
Returns : It returns 1 if the given date is in range January-March. 2 for April-June.3 for July-September and if the date is in the range from October-December it returns 4.
Example-1 :
Finding the Current QUARTER Using QUARTER() Function.
SELECT QUARTER(NOW()) AS CURRENT_QUARTER;
Output :
Example-2 :
Finding the Quarter from given DateTime Using QUARTER() Function.
SELECT QUARTER('2020-04-26 08:09:22') AS QUARTER_NUMBER;
Output :
Example-3 :
Finding the Quarter from given DateTime Using QUARTER() Function when the date is NULL.
SELECT QUARTER(NULL) AS QUARTER_NUMBER;
Output :
Example-4 :
The QUARTER function can also be used to find the total product sold for every QUARTER. To demonstrate create a table named.
Product :
CREATE TABLE Product(
Product_id INT AUTO_INCREMENT,
Product_name VARCHAR(100) NOT NULL,
Buying_price DECIMAL(13, 2) NOT NULL,
Selling_price DECIMAL(13, 2) NOT NULL,
Selling_Date Date NOT NULL,
PRIMARY KEY(Product_id)
);
Now insert some data to the Product table :
INSERT INTO
Product(Product_name, Buying_price, Selling_price, Selling_Date)
VALUES
('Audi Q8', 10000000.00, 15000000.00, '2018-01-26' ),
('Volvo XC40', 2000000.00, 3000000.00, '2018-04-20' ),
('Audi A6', 4000000.00, 5000000.00, '2018-07-25' ),
('BMW X5', 5000500.00, 7006500.00, '2018-10-18' ),
('Jaguar XF', 5000000, 7507000.00, '2018-01-27' ),
('Mercedes-Benz C-Class', 4000000.00, 6000000.00, '2018-04-01' ),
('Jaguar F-PACE', 5000000.00, 7000000.00, '2018-12-26' ),
('Porsche Macan', 6500000.00, 8000000.00, '2018-04-16' ) ;
So, Our table looks like :
Product_id |
Product_name |
Buying_price |
Selling_price |
Selling_Date; |
1 |
Audi Q8 |
10000000.00 |
15000000.00 |
2018-01-26 |
2 |
Volvo XC40 |
2000000.00 |
3000000.00 |
2018-04-20 |
3 |
Audi A6 |
4000000.00 |
5000000.00 |
2018-07-25 |
4 |
BMW X5 |
5000500.00 |
7006500.00 |
2018-10-18 |
5 |
Jaguar XF |
5000000.00 |
7507000.00 |
2019-01-27 |
6 |
Mercedes-Benz C-Class |
4000000.00 |
6000000.00 |
2019-04-01 |
7 |
Jaguar F-PACE |
5000000.00 |
7000000.00 |
2019-12-26 |
8 |
Porsche Macan |
6500000.00 |
8000000.00 |
2020-04-16 |
Now, we are going to find the number of products sold per quarter by using the MONTH () function.
SELECT
QUARTER(Selling_Date) as quarter,
COUNT(Product_id) as PRODUCT_SOLD
FROM
Product
GROUP BY QUARTER(Selling_Date)
ORDER BY QUARTER(Selling_Date);
Output :
QUARTER |
PRODUCT_SOLD |
1 |
2 |
2 |
3 |
3 |
1 |
4 |
2 |
Last Updated :
02 Dec, 2020
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...