Click here to Skip to main content
14,971,097 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi,
I need to retrieve data from MySQL tables and to retrieve as follows:
['Years', 'January', 'February', 'March','April','May', 'June','July', 'August', 'September','October','November','December',{ role: 'annotation' }],
['2012', 10, 24, 20, 32, 18, 5,10, 24, 20, 32, 18, 5,'2012']'
['2013', 0, 22, 23, 30, 0, 9,0, 22, 23, 30, 0, 9,'2013'],
['2014', 10, 24, 20, 32, 18, 5,10, 24, 20, 32, 18, 5,'2014'],
['2015', 0, 22, 23, 30, 0, 9,0, 22, 23, 30, 0, 9,'2015'],
['2016', 0, 0, 0, 0, 0, 0,10, 0, 0, 0, 0, 0,'2016'],
['2017', 16, 22, 23, 30, 0, 9,0, 22, 23, 30, 0, 9,'2017'],
['2018', 10, 24, 20, 32, 18, 5,10, 24, 20, 32, 18, 5,'2018'],
['2019', 0, 22, 23, 30, 0, 9,0, 22, 23, 30, 0, 9,'2019'],
['2020', 28, 19, 29, 30, 12, 13,28, 19, 29, 30, 12, 13,'2020']

To display values in Google chart(https://i.imgur.com/n1grcto.png).

I am having two tables "user" and "usertypes", in user table there is only one date column which stores the date of joining(say 2020-05-12). I want to retrieve the data for the current year(say 2020).

My output needs to be:
|Year|jan|feb|mar|..|may|..|dec|
|2020| 0 | 0 | 0 |..|1|....| 0 |
|2019| 5 | 0 | 4 |..|0|....| 2 |
|2018| 0 |12 |44 |..|0|....| 0 |
...
|2012| 0 | 4 | 1 |..|0|....| 5 |

So that I able to frame the structure what the Google chart is expecting for Stacked column chart.

Kindly suggest me ideas.

Thank You in advance.

What I have tried:

I didn't try as much but I query with error or cannot frame the structure.
Posted
Updated 12-Jan-21 6:09am

You've given a list of requirements; and no work.

Perhaps start by "learning SQL"; so you can "query" how many joined for a given year and month.

Then you "pivot" the results; adding little vertical bars between the results as "delimiters" ... something you could do with a csv file.
   
Something like this should work:
SQL
SELECT
    Years.`Year`,
    (SELECT COUNT(1) FROM YourTable As m WHERE Year(m.join_date) = Years.`Year` And Month(m.join_date) = 1) As `Jan`,
    (SELECT COUNT(1) FROM YourTable As m WHERE Year(m.join_date) = Years.`Year` And Month(m.join_date) = 2) As `Feb`,
    ...
FROM
    (SELECT DISTINCT Year(join_date) As `Year` FROM YourTable) As Years
ORDER BY
    Years.`Year`
;
MySQL :: MySQL 8.0 Reference Manual :: 13.2.11.1 The Subquery as Scalar Operand[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900