0

I have a table named 'mrtg' that has four columns:

mysql> select * from mrtg;
+----------+------+------+------+
| city     | day1 | day2 | day3 |
+----------+------+------+------+
| Delhi    |    2 |    6 |    9 |
| Mumbai   |    1 |    3 |    8 |
| Banglore |    4 |    1 |    6 |
+----------+------+------+------+
3 rows in set (0.00 sec)

If current date = '2019-09-01' then I want to select as

SELECT CITY, DAY1 FROM MRTG;

If current date = '2019-09-02' then I want to select as

SELECT CITY, DAY1, DAY2 FROM MRTG;

If current date = '2019-09-02' then I want to select as

SELECT CITY, DAY1, DAY2, DAY3 FROM MRTG;

and so on.

How can I write a query for this?

The values represent day-wise data for a whole month. I do wonder about this type of table structure, but it's the client requirement. I need to find a solution to fetch only the columns from day 1 to the current date.

0

2 Answers 2

0

If you were able to start with a new table I would use this structure to store the data:

CREATE TABLE mrtg (
    city varchar(30)
    , logdate date
    , value int
)

This way you can store data for any time period and work out the days needed from the date.

Since you can't create that we'll have to see if we can modify what you've got in a query. I've had a play and this will get the data into a table format that I believe would be easier to work with:

select data.*
  from mrtg
  left join (
    select city, 1 as day, day1 as datavalues from mrtg
    union all
    select city, 2 as day, day2 from mrtg
    union all
    select city, 3 as day, day3 from mrtg
    #.... 
    #union all
    #select city, 4 as day, day4 from mrtg
  ) data
  on mrtg.city = data.city
  where data.day <= DAYOFMONTH(STR_TO_DATE('2019-08-02','%Y-%m-%d'))

You'll have to add as many union all elements as you have columns but then you can filter on the number of days you want. You will now be able to GROUP BY and perform any calculations you need to.

select data.city, sum(data.datavalues) as TotalToDate
  from mrtg
  left join (
    select city, 1 as day, day1 as datavalues from mrtg
    union all
    select city, 2 as day, day2 from mrtg
    union all
    select city, 3 as day, day3 from mrtg
    #.... 
    #union all
    #select city, 4 as day, day4 from mrtg
  ) data
  on mrtg.city = data.city
  where data.day <= DAYOFMONTH(STR_TO_DATE('2019-08-02','%Y-%m-%d'))
  GROUP BY city

For keeping your main queries cleaner and smaller I'd recommend creating a VIEW for the subquery with all of the UNION ALL statements in.

6
  • Thanks for detailed explanation. My requirement is fulfilled to some extent but not completely yet.
    – AAMIR KHAN
    Commented Sep 4, 2019 at 7:09
  • Is there any query or proc which select ONLY non-zero/not null COLUMNS from table. If all the rows of any of the column contains NULL/0 then ignore that column.
    – AAMIR KHAN
    Commented Sep 4, 2019 at 7:14
  • What is your requirement? Do you need the data to be displayed in the tabular form used in your example?
    – KevH
    Commented Sep 4, 2019 at 7:58
  • yes. Suppose I've 10 columns out off this 3 columns are totally '0' value. I want to fetch ONLY those 7 columns which are not zero values.
    – AAMIR KHAN
    Commented Sep 4, 2019 at 8:01
  • Using the structure in my answer you could filter out any non zero columns. How is the query being displayed to the end user? Could the display be handled within the interface they are using (e.g. let the website handle the layout?) or are they using the query directly in within MySQL?
    – KevH
    Commented Sep 4, 2019 at 8:07
0

You can try using MySQL JSON type to store the day and value data, as shown here:

CREATE TABLE days (
  id INT PRIMARY KEY,
  city VARCHAR(10),
  data JSON
)

The data column is of type JSON array. Each array element has the day field values 1 to 31, and the val field value as a number. For example, for the first day of a month the data is { "day": 1, "val": 7 }.

Insert some sample data into the table.

INSERT INTO days VALUES (1, 'Rome', '[ { "day": 10, "val": 7 }, { "day": 11, "val": 22 }, { "day": 12, "val": 3 } ]');

I need to find a solution to fetch only the columns from day 1 to the current date.

The query, to get the values until the 11th day:

SET @today = DAYOFMONTH('2023-12-11'); -- for todays day use DAYOFMONTH(CURDATE());

SELECT id, city, JSON_ARRAYAGG(JSON_OBJECT("day", jtab.day, "val", jtab.val)) AS result_data 
FROM 
  days, 
  JSON_TABLE(data, '$[*]' COLUMNS(day INT path '$.day', val INT path '$.val')) AS jtab 
WHERE 
  jtab.day <= @today AND
  jtab.val > 0 AND
  id = 1
GROUP BY id;

The output:

+----+------+---------------------------------------------------+
| id | city | result_data                                       |
+----+------+---------------------------------------------------+
|  1 | Rome | [{"day": 10, "val": 7}, {"day": 11, "val": 22}]   |
+----+------+---------------------------------------------------+

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.