bill_no | payment_dt | amount |
---|---|---|
34521 | 2015-03-04 | 3412 |
56732 | 2015-03-09 | 8796 |
67598 | 2015-04-11 | 5438 |
SELECT YEAR(payment_dt),COUNT(*) FROM `plus2_bills`
GROUP BY YEAR(payment_dt)
YEAR(payment_dt) | COUNT(*) |
---|---|
2015 | 8 |
2016 | 8 |
2017 | 8 |
2018 | 2 |
SELECT YEAR(payment_dt),COUNT(*) as Nos,SUM(amount) as total,
AVG(amount) as average FROM `plus2_bills` GROUP BY YEAR(payment_dt)
YEAR(payment_dt) | Nos | total | average |
---|---|---|---|
2015 | 8 | 43764 | 5470.5000 |
2016 | 8 | 46202 | 5775.2500 |
2017 | 8 | 40602 | 5075.2500 |
2018 | 2 | 9105 | 4552.5000 |
SELECT MONTHNAME(payment_dt) as m ,COUNT(*) as Nos,
SUM(amount) as total,AVG(amount) as average
FROM `plus2_bills` GROUP BY m
m | Nos | total | average |
---|---|---|---|
April | 2 | 11172 | 5586.0000 |
August | 2 | 10186 | 5093.0000 |
December | 2 | 10002 | 5001.0000 |
February | 2 | 14412 | 7206.0000 |
January | 2 | 9095 | 4547.5000 |
July | 2 | 9103 | 4551.5000 |
June | 2 | 11399 | 5699.5000 |
March | 4 | 21313 | 5328.2500 |
May | 2 | 11319 | 5659.5000 |
October | 4 | 19610 | 4902.5000 |
September | 2 | 12062 | 6031.0000 |
SELECT CONCAT(YEAR(payment_dt),'-',MONTHNAME(payment_dt)) as ym ,
COUNT(*) as Nos,SUM(amount) as total,AVG(amount) as average
FROM `plus2_bills` GROUP BY ym
ym | Nos | total | average |
---|---|---|---|
2015-April | 2 | 11172 | 5586.0000 |
2015-March | 2 | 12208 | 6104.0000 |
2015-October | 2 | 8322 | 4161.0000 |
2015-September | 2 | 12062 | 6031.0000 |
2016-February | 2 | 14412 | 7206.0000 |
2016-July | 2 | 9103 | 4551.5000 |
2016-June | 2 | 11399 | 5699.5000 |
2016-October | 2 | 11288 | 5644.0000 |
2017-August | 2 | 10186 | 5093.0000 |
2017-December | 2 | 10002 | 5001.0000 |
2017-January | 2 | 9095 | 4547.5000 |
2017-May | 2 | 11319 | 5659.5000 |
2018-March | 2 | 9105 | 4552.5000 |
SELECT
CONCAT( YEAR( payment_dt ) , '-', MONTHNAME( payment_dt ) , '-',
IF( DAY( payment_dt ) <=15, 'M-1', 'M-2' ) ) AS ym,
COUNT( * ) AS Nos, SUM( amount ) AS total, AVG( amount ) AS average
FROM `plus2_bills`
GROUP BY ym
ym | Nos | total | average |
---|---|---|---|
2015-April-M-1 | 1 | 5438 | 5438.0000 |
2015-April-M-2 | 1 | 5734 | 5734.0000 |
2015-March-M-1 | 2 | 12208 | 6104.0000 |
2015-October-M-1 | 1 | 4536 | 4536.0000 |
2015-October-M-2 | 1 | 3786 | 3786.0000 |
2015-September-M-1 | 2 | 12062 | 6031.0000 |
2016-February-M-1 | 1 | 6754 | 6754.0000 |
------ | - | --- | ----- |
SELECT
CASE WHEN MONTH(payment_dt)>=4
THEN CONCAT(YEAR(payment_dt), '-',YEAR(payment_dt)+1)
ELSE concat(YEAR(payment_dt)-1,'-',YEAR(payment_dt))
END AS financial_year ,
COUNT(*) as Nos,SUM(amount) as total,AVG(amount) as average
FROM `plus2_bills` GROUP BY financial_year
Query using CASE WHEN THEN END matching
financial_year | Nos | total | average |
---|---|---|---|
2014-2015 | 2 | 12208 | 6104.0000 |
2015-2016 | 8 | 45968 | 5746.0000 |
2016-2017 | 8 | 40885 | 5110.6250 |
2017-2018 | 8 | 40612 | 5076.5000 |
SELECT
CASE WHEN MONTH(payment_dt)>=10
THEN CONCAT(YEAR(payment_dt), '-',YEAR(payment_dt)+1)
ELSE concat(YEAR(payment_dt)-1,'-',YEAR(payment_dt))
END AS financial_year ,
COUNT(*) as Nos,SUM(amount) as total,AVG(amount) as average
FROM `plus2_bills` GROUP BY financial_year
Financial year starting from 1st July to 30th June
SELECT
CASE WHEN MONTH(payment_dt)>=7
THEN CONCAT(YEAR(payment_dt), '-',YEAR(payment_dt)+1)
ELSE concat(YEAR(payment_dt)-1,'-',YEAR(payment_dt))
END AS financial_year ,
COUNT(*) as Nos,SUM(amount) as total,AVG(amount) as average
FROM `plus2_bills` GROUP BY financial_year
SELECT
CASE WHEN MONTH(payment_dt)>=4
THEN CONCAT(YEAR(payment_dt), '-',DATE_FORMAT(payment_dt,'%y')+1)
ELSE concat(YEAR(payment_dt)-1,'-',DATE_FORMAT(payment_dt,'%y'))
END AS financial_year ,
COUNT(*) as Nos,SUM(amount) as total,AVG(amount) as average
FROM `plus2_bills` GROUP BY financial_year
financial_year | Nos | total | average |
---|---|---|---|
2014-15 | 2 | 12208 | 6104.0000 |
2015-16 | 8 | 45968 | 5746.0000 |
2016-17 | 8 | 40885 | 5110.6250 |
2017-18 | 8 | 40612 | 5076.5000 |
SELECT CONCAT(YEAR(payment_dt), '-Q',quarter(payment_dt)) as qt_year ,
COUNT(*) as Nos,SUM(amount) as total,AVG(amount) as average
FROM `plus2_bills` GROUP BY qt_year
QUARTER: to get data on different Quarters of Financial years
Andrew Ames | 22-10-2010 |
What if your date column is a datetime column and the logins are at various times of the day? it then groups by the date and time showing numerous rows for each day? cheers |
smo | 30-10-2010 |
Solution to this is added to the tutorial, thanks. |
gani | 12-10-2011 |
hi i need to retrive data from table between specific range of date i am geeting the range from users like String fromdate=fyear+"-"+fmonth+"-"+fdate; String todate=tyear+"-"+tmonth+"-"+tdate; will u plz send me the query for this |
Nashirmohammad Marediya | 07-05-2014 |
I need like this type of result how can i ? which query i should apply. track_id || dt 126 - 129 || 2010-07-07 130 - 155 || 2010-07-08 179 - 181 || 2010-07-09 |
13-09-2019 | |
I am using below query SELECT CONCAT( YEAR( payment_dt ) , '-', MONTHNAME( payment_dt ) , '-', IF( DAY( payment_dt ) <=15, 'M-1', 'M-2' ) ) AS ym, COUNT( * ) AS Nos, SUM( amount ) AS total, AVG( amount ) AS average FROM `plus2_bills` GROUP BY ym its working fine. Now i want to show data 1 to 15 if date is 16 and 16-30/31 if date is first of next month using query for example : List like : Date 2019-August-M-1 2019-August-M-2 2019-September-M-1 so i want 2019-September-M-1 comes on 16 sep. |
27-09-2019 | |
Solution to this is added in the content of this article |