using postgresql/mysql I have a table as drop table if exists test_score; CREATE TABLE sale2 ( year double precision, mo
Posted: Fri Apr 29, 2022 6:52 am
using postgresql/mysql I have a table as
drop table if exists test_score; CREATE TABLE sale2 ( year
double precision, month integer, n integer, s integer ); INSERT
INTO sale2 (year, month, n, s) VALUES (2019, 1, 1, 37) , (2019, 1,
2, 63) , (2019, 1, 3, 22) , (2019, 1, 4, 27) , (2019, 2, 1, 27) ,
(2019, 2, 2, 40) , (2019, 2, 3, 76) , (2019, 2, 4, 24) , (2019, 3,
1, 46) , (2019, 3, 2, 74) , (2019, 3, 3, 23) , (2019, 3, 4, 95) ,
(2019, 4, 1, 65) , (2019, 4, 2, 45) , (2019, 4, 3, 33) , (2019, 4,
4, 64) , (2019, 5, 1, 3) , (2019, 5, 2, 7) , (2019, 5, 3, 100) ,
(2019, 5, 4, 65) , (2019, 6, 1, 59) , (2019, 6, 2, 58) , (2019, 6,
3, 19) , (2019, 6, 4, 66) , (2019, 7, 1, 47) , (2019, 7, 2, 88) ,
(2019, 7, 3, 79) , (2019, 7, 4, 97) , (2019, 8, 1, 84) , (2019, 8,
2, 98) , (2019, 8, 3, 90) , (2019, 8, 4, 21) , (2019, 9, 1, 61) ,
(2019, 9, 2, 12) , (2019, 9, 3, 48) , (2019, 9, 4, 88) , (2019, 10,
1, 52) , (2019, 10, 2, 24) , (2019, 10, 3, 13) , (2019, 10, 4, 97)
, (2019, 11, 1, 98) , (2019, 11, 2, 36) , (2019, 11, 3, 92) ,
(2019, 11, 4, 63) , (2019, 12, 1, 81) , (2019, 12, 2, 25) , (2019,
12, 3, 27) , (2019, 12, 4, 84);
I need to get the maximum total sales and month and year
right now I have something like this
select year, month, tt from (select year, month, sum(s) tt from
sale2 group by year, month) t1
order by tt desc;
all what I need is to choose the first row only.
drop table if exists test_score; CREATE TABLE sale2 ( year
double precision, month integer, n integer, s integer ); INSERT
INTO sale2 (year, month, n, s) VALUES (2019, 1, 1, 37) , (2019, 1,
2, 63) , (2019, 1, 3, 22) , (2019, 1, 4, 27) , (2019, 2, 1, 27) ,
(2019, 2, 2, 40) , (2019, 2, 3, 76) , (2019, 2, 4, 24) , (2019, 3,
1, 46) , (2019, 3, 2, 74) , (2019, 3, 3, 23) , (2019, 3, 4, 95) ,
(2019, 4, 1, 65) , (2019, 4, 2, 45) , (2019, 4, 3, 33) , (2019, 4,
4, 64) , (2019, 5, 1, 3) , (2019, 5, 2, 7) , (2019, 5, 3, 100) ,
(2019, 5, 4, 65) , (2019, 6, 1, 59) , (2019, 6, 2, 58) , (2019, 6,
3, 19) , (2019, 6, 4, 66) , (2019, 7, 1, 47) , (2019, 7, 2, 88) ,
(2019, 7, 3, 79) , (2019, 7, 4, 97) , (2019, 8, 1, 84) , (2019, 8,
2, 98) , (2019, 8, 3, 90) , (2019, 8, 4, 21) , (2019, 9, 1, 61) ,
(2019, 9, 2, 12) , (2019, 9, 3, 48) , (2019, 9, 4, 88) , (2019, 10,
1, 52) , (2019, 10, 2, 24) , (2019, 10, 3, 13) , (2019, 10, 4, 97)
, (2019, 11, 1, 98) , (2019, 11, 2, 36) , (2019, 11, 3, 92) ,
(2019, 11, 4, 63) , (2019, 12, 1, 81) , (2019, 12, 2, 25) , (2019,
12, 3, 27) , (2019, 12, 4, 84);
I need to get the maximum total sales and month and year
right now I have something like this
select year, month, tt from (select year, month, sum(s) tt from
sale2 group by year, month) t1
order by tt desc;
all what I need is to choose the first row only.