CODEWITHSUNDEEP
mysqldatesum
Valerius Romulus
Valerius Romulus

Reputation: 55

Select and show according to the year/monts

can you help me to select, and sum all the values from the table, and show them according to the months/this year?

Table looks as follow"

| ID | value | reg_date            | 
| 1  | 2     | 2017-01-11 02:26:22 |
| 2  | 2     | 2017-03-12 12:22:23 |
| 3  | 2     | 2017-04-13 08:26:33 |
| 4  | 3     | 2017-04-15 12:26:16 |
| 5  | 5     | 2017-05-15 19:26:13 |
| 6  | 2     | 2017-06-14 17:12:16 |
| 7  | 6     | 2017-07-12 14:26:16 |
| 8  | 1     | 2015-09-11 13:23:16 |
| 9  | 1     | 2016-09-05 12:26:34 |
| 10 | 1     | 2017-12-11 19:11:45 |

And I would like to get something like:

| value  | reg_date                 |
|    2   |  2017-01                 |
|    0   |  2017-02                 |
|    2   |  2017-03                 |
|    5   |  2017-04                 |
|    5   |  2017-05                 |
|    2   |  2017-06                 |
|    6   |  2017-07                 |
|    0   |  2017-08                 |
|    2   |  2017-09                 |
|    0   |  2017-10                 |
|    0   |  2017-11                 |
|    1   |  2017-12                 |

Upvotes: 1

Views: 54

Answers (3)

Valerius Romulus
Valerius Romulus

Reputation: 55

Also works something like:

SELECT start_date, 
IFNULL(TIME_FORMAT(SEC_TO_TIME (sum(value)),'%l.%i' ),0)
as time01 FROM table WHERE YEAR(start_date) = YEAR(CURDATE())
and MONTH(start_date) = 01

SELECT start_date, 
IFNULL(TIME_FORMAT(SEC_TO_TIME (sum(value)),'%l.%i' ),0)
as time02 FROM table WHERE YEAR(start_date) = YEAR(CURDATE())
and MONTH(start_date) = 02
....

Thanks guys a lot.

Upvotes: 0

Jai
Jai

Reputation: 466

I tried following approach to solve your problem: 

--created sample table datte which will contain following records 

create table datte (id int identity(1,1), value int, reg_date datetime);

insert into datte(value,reg_date) values (2,'2017-01-11 02:26:22');
insert into datte(value,reg_date) values (2,'2017-03-12 12:22:23');
insert into datte(value,reg_date) values (2,'2017-04-13 08:26:33');
insert into datte(value,reg_date) values (3,'2017-04-15 12:26:16');
insert into datte(value,reg_date) values (5,'2017-05-15 19:26:13');
insert into datte(value,reg_date) values (2,'2017-06-14 17:12:16');
insert into datte(value,reg_date) values (6,'2017-07-12 14:26:16');
insert into datte(value,reg_date) values (1,'2015-09-11 13:23:16');
insert into datte(value,reg_date) values (1,'2016-09-05 12:26:34');
insert into datte(value,reg_date) values (1,'2017-12-11 19:11:45');

then i created a table type to store the year list

 create type [values] as table(
[year] int
);

You can also use temp table here.

    -- create temp table to store the output
create table #tempdate([year] varchar(1000), [count] int);

--create a  variable of table type values
declare @years as [values] ,
--create following variable to process the data


    @year int,  @minyear int,@maxyear int;

     --insert years in table type variable @years
    insert into @years 
select YEAR(reg_date) from datte
group by YEAR(reg_date)

 --store min year and max year value in variable for processing 
select @minyear = min([year]) from @years;
select @maxyear = max([year]) from @years;

 -- logic to create query to get desired output
while(@minyear <= @maxyear)
begin
SET @YEAR = @minyear;

IF EXISTS(SELECT 1 FROM DATTE WHERE REG_DATE LIKE '%'+CONVERT(VARCHAR,@YEAR)+'%')
BEGIN

    --insert the yearly in temp table
    insert into  #tempdate 
     select   cast(convert(varchar,@year) as varchar) + '-' +cast(m.number as varchar) , isnull(total_qty,0) as total_qty
    from (
     select number
     from    master.dbo.spt_values 
     where type = 'P' and number between 01 and 12
    ) m 
    left join (
        select   MONTH(reg_date) as mth, SUM(value) as total_qty
        from     datte
        where    YEAR(reg_date) = @year
        group by MONTH(reg_date)
    ) s on m.number = s.mth ;

    END

SET @MINYEAR = @MINYEAR + 1;
end
--print the data 
select [count],[year] from  #tempdate

hope this helps, Jai

Upvotes: 3

Tk42421
Tk42421

Reputation: 56

You can format how date is displayed and group by this results

SELECT count(ID), DATE_FORMAT(reg_date, "%Y-%m") AS rd FROM tablename GROUP BY rd

Upvotes: 0

Related Questions