0

I have made a query which shows all hits recorded for a particular region. It goes something like this:

Unique Visits by Region

SELECT
         r.field_region_for_real_value as region,  Count(*) as count
        FROM
          login_activity l, users u, field_data_field_region_for_real r
        where l.uid = u.uid AND u.uid = r.entity_id

        Group by r.field_region_for_real_value

The results of this are as follows:

region       |  count
----------------------
Asia              23
EEMA              14
Latin America     73
North America     61
Western Europe    47

But now what I am trying to do is to change the way this data is represented. I am hoping that instead of grouped by region, it is grouped by month. Within the month, I am hoping to have all values of each region that have a data falling in that month.

For example, here is the result I am hoping to achieve:

    month          |   Asia    |   EEMA    |   Latin America    |   North America    |   Western Europe 
    ----------------------------------------------------------------------------------------------------
    Jan                23         43              23                  43                    53           
    Feb                63         3               13                  39                    18 
    Mar                23         39              42                  29                    17
    Apr                49         56              50                  64                    98
    May                63         3               13                  39                    18
    Jun                21         92              15                  23                    19
    Jul                49         56              50                  64                    98
    Aug                63         3               13                  39                    18
    Sep                63         3               13                  39                    18
    Oct                23         39              42                  29                    17
    Nov                23         43              23                  43                    53
    Dec                21         92              15                  23                    19

The date which needs to be processed comes from the table login_activity. The fields of the table are as follows:

aid              int .  (Unique ID)
uid              int
hostname         varchar
timestamp        int    (Date)
Steve
  • 2,546
  • 8
  • 49
  • 94
  • We stopped writing queries this way ca. 1992. Come. JOIN us. As for the rest, seriously consider handling issues of data display in application code. – Strawberry Feb 20 '18 at 07:32
  • I understand :) but the data has to come through query as this needs to be extracted on the go – Steve Feb 20 '18 at 07:33
  • This is a frequent question on SO. Try googling mysql pivot and mysql conditional aggregation and heres some links to start you https://stackoverflow.com/questions/7674786/mysql-pivot-table https://modern-sql.com/use-case/pivot – P.Salmon Feb 20 '18 at 07:34
  • Extracted on the go? Extracted by what? For what? Humans care about display formats. Computers generally don't. – Strawberry Feb 20 '18 at 07:37
  • What I meant was that I have to extract this data from the sql query – Steve Feb 20 '18 at 07:52
  • any assistance here please – Steve Feb 20 '18 at 19:59

0 Answers0