Question 112826 in Mysql Question posted in
The official MySQL documentation can be found here.

Getting formatted result in mysql table

enter image description here

This is my input table

but i want to get this table

enter image description here

Explaination:
I want to subtract value of segmeted 14/10/22 – 7/10/22 that means (28930-28799)

how could i get this kindly help me to figure it out. I cant format it properly.

enter image description here

This is my table

and i want to subtract value column subtraction by SEGMENTED_DATE wise
like (14th october value – 7th october value) that means (28930-28799)

the segment table is created by bellow query

select segment ,count(distinct user_id)as value,SEGMENTED_DATE  from weekly_customer_RFM_TABLE
     where segment in('About to sleep','Promising','champion','Loyal_customer',
                'Potential_Loyalist','At_Risk','Need_Attention','New_customer',
                'Hibernating','Cant_loose')
     and  SEGMENTED_DATE between '2022-10-07' and '2022-10-28'
Group by segment,SEGMENTED_DATE

I want this table as output

enter image description here

This is only value difference only Segment_date wise

Tags:

2

Answers


  1. 0

    The sample data of results table is not correct.
    You said that
    "I want to subtract value of segmeted 14/10/22 – 7/10/22 that means (28930-28799) " but this gives 131 not 233.
    You said that "while in you example and value 21/10/22 -14/10/22 that means(29137-28930)" but this gives 207 not 190.
    How did you calculate the value 344 in the first row?

    The following query will produce the format you want but without the first row as it is not clear to me how did you calculate it. I put xxx AS your table name. The query is based on using variables.

    SET @Prev = 0;
SET @i = 0;

SELECT CONCAT('Week', C, '-', 'Week', C-1) AS Change_Time, Segment, Prev AS Value FROM (
  SELECT `Value`- @Prev AS Prev, Segment, @Prev :=`Value` AS V, @i:=@i+1 AS C, Segmentd FROM xxx
) AS t WHERE C> 1;

    The results will be :

    This query is suitable for MySQL engine and will not run on SQL server.

    Edit1:

    Here is some explanation:
    In inner query I used variables for two reasons:

    1. I need a counter (@i) so I can know week index like (week1, week2, …). This counter will increase with each record by (@i:=@i+1).
    2. I need to know value of previous record so I used (@Prev :=Value) to save that value then I can subtract it from Value in current record (Value– @Prev) AS Prev.

    I started with initial values (SET @Prev = 0;) Assuming no previous values and (SET @i = 0;) because @i will increased to (1) at first record.

    In outer query I converted (@i named C) to (week(i)-week(i-1)) week1-week0, week2-week1, ....... and removed first record because it will display wrong data.
    I can help improving the query if you show me some real data.

    Edit2:

    According to you last modification at 2022/10/07 the query will be :

    SET @Prev = 0;
SET @S = 0;
SELECT Segment, Diffirence, SEGMENTED_DATE FROM (
  SELECT 
    `Value`- @Prev AS Diffirence,  
    POSITION(@S IN Segment) AS NotFirst, 
    @Prev := IF(@S=Segment, `Value`, 0) AS `Value`, 
    @S := Segment AS Segment,
    SEGMENTED_DATE 
  FROM test
) AS t WHERE NotFirst> 0;
    Login or Signup to reply.
  2. 0

    You may perform a self join as the following:

    SET @rn=1;

SELECT T.segment,
       D.value-T.value AS Difference,
       D.segmented_date,
FROM table_name T JOIN table_name D
ON D.segmented_date=T.segmented_date + INTERVAL 7 DAY
   AND D.segment=T.segment
ORDER BY T.segment, D.segmented_date

    See a demo.

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

Back To Top
Search