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

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.

|improve this answer|||||
  • Thanks for detailed explanation. My requirement is fulfilled to some extent but not completely yet. – AAMIR KHAN Sep 4 '19 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 Sep 4 '19 at 7:14
  • What is your requirement? Do you need the data to be displayed in the tabular form used in your example? – KevH Sep 4 '19 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 Sep 4 '19 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 Sep 4 '19 at 8:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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