phpmyadmin Question posted in
The official documentation can be found here.

MySQL – Sum of digits of year – Phpmyadmin

I need to get the sum of digits of year in format date, for example 1997 = 1+9+9+7 = 26. I have to do it in MySQL with SELECT only. I’ve tried to use SUM function, but it sums only years in a few rows.

Table name: list.

enter image description here

In phpMyAdmin, I’m executing the following command and getting the following results:

enter image description here

Tags:

2

Answers


  1. 0

    You can do arithmetics for this:

    set @year = 1997;    
select 
    floor(@year / 1000) 
    + floor(@year % 1000 / 100) 
    + floor(@year % 100 / 10)
    + floor(@year % 10) res

    Yields:

    26

    Starting from a date or datetime value, you can use year() to extract the year part first:

    set @mydate = '1997-01-01';    
select 
    floor(year(@mydate) / 1000) 
    + floor(year(@mydate) % 1000 / 100) 
    + floor(year(@mydate) % 100 / 10)
    + floor(year(@mydate) % 10) res
    Login or Signup to reply.
  2. 0

    Or you can use something like this.
    Result for 1997-01-01

    SELECT @result:=
    @First:= LEFT(year,1)+
    @Second:= SUBSTRING(year,2,1)+ 
    @Third:= SUBSTRING(year,3,1)+
    @forth:= SUBSTRING(year,4,1) 
FROM year;

    ---

    **Query #1**

    SELECT * FROM year;

| id  | year       | result |
| --- | ---------- | ------ |
| 1   | 1997-01-01 |        |
| 2   |            | 26     |

    ---

    View on DB Fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search