How to group by Datafields?

Wappler General How To
#1

I’m trying to show a User Signup Dashboard, but I get December multiple times. What should I change?

I mean now I query user STATUS + USER_REGDATE

So isnt that wrong? I mean what if I have no signups in one month, then it just doesnt show me that month? I need to have a query where every Month is counted, and then Map these USER_REGDATE… hmmmm

image
image.png1800×947 126 KB

Using Alternative charts with wappler query
#2

the query can only show the data which is available so no signups means no data. Perhaps you could force all months with some sort of right join to a table containing all the month names. without more detail can’t be too specific

#3

you should create a view in your database then use it in wappler

#4

a view still wont add missing months due to no data for that month. Freddy wants an entry even if month is missing?

#5

see this

#6

Oh okay pretty uncool …

Isn’t there a different solution? Or maybe to use like variable or fake months as “query aliases”. I see that could be tricky with wappler. I mean is it a DB thing or more an APP thing … ?

How to put that strange query into Wappler…?

  SELECT COUNT(u.userID) AS total, m.month
     FROM (
           SELECT 'Jan' AS MONTH
           UNION SELECT 'Feb' AS MONTH
           UNION SELECT 'Mar' AS MONTH
           UNION SELECT 'Apr' AS MONTH
           UNION SELECT 'May' AS MONTH
           UNION SELECT 'Jun' AS MONTH
           UNION SELECT 'Jul' AS MONTH
           UNION SELECT 'Aug' AS MONTH
           UNION SELECT 'Sep' AS MONTH
           UNION SELECT 'Oct' AS MONTH
           UNION SELECT 'Nov' AS MONTH
           UNION SELECT 'Dec' AS MONTH
          ) AS m
LEFT JOIN users u 
ON MONTH(STR_TO_DATE(CONCAT(m.month, ' 2013'),'%M %Y')) = MONTH(u.userRegistredDate)
   AND YEAR(u.userRegistredDate) = '2013'
GROUP BY m.month
ORDER BY 1+1;

Pretty hard but common problem: Faking empty ranges…

I mean SQL would be like Stored Procedures …

Hehe yes thats gonna be TRICKY

#7

I doubt it you could put this query into Wappler (it’s an example of why it would useful to have an ‘advanced’ option to write out own queries, including variables from Wappler - but I have no idea how feasible this would be).

I haven’t done what you’re trying to do in Wappler (or even MySQL), but I have come across similar situation and, like @Hyperbytes suggested, I would create a months table and get your totals from the other end as it were.

#8

you create a view in the database then use it just like any table with wappler
i belive you can do it with PHP or with JS
you can create a table that have all the months in it too.
there is many ways to achieve the end goal. find what you like :slight_smile:

#9

Here is one simpler approach but I’ll try to figure it out with views …

select * from 
    (select 2000 as year union
     select 2001 as year union
     select 2009
    ) as years, 
    (select 1 as month union 
     select 2 as month union 
     select 3 as month union 
     select 4 as month union 
     select 5 as month union 
     select 6 as month union 
     select 7 as month union 
     select 8 as month union 
     select 9 as month
     )
AS months 
WHERE year between 2001 AND 2008 OR (year=2000 and month>0) OR (year = 2009 AND month < 4) 
ORDER by year,month

BTW @George would it be possible to have more mysql functions like (https://www.w3schools.com/sql/sql_ref_mysql.asp) in Query Builder (now I only have SUM, AVG, COUNT, etc). I know thats maybe DB specific, as some people may use other DB types… or what you think? This question is a bit offtopic…

#10

you just add CREATE VIEW 'accounts_v_members' AS
in the beginning

#11

on beach a moment so pseudo code only but try something like

SELECT COUNT(u.userID) AS total, m.month
FROM table right join monthlist on m.month = monthlist.month.

where monthlist is a simple list of months. the right join will force the month to be added via the join even if no data is present.

1 Like
#12

@Hyperbytes that is a very genius IDEA !!! I know exactly what you mean. So to have a JOIN which just uses all the MONTHS from that t_months and matches on the other side the specific registration dates ! :smiley: I’m sure thats gonna work and with wappler clicky clicky GUI’s :wink:

#13

almost anything can be done with joins if you know how!

1 Like
#14

True! But for me its still a 10% Workaround/Quirk … but simple. I mean the question is would it also work for 2 years timeframe? Just for fun . like: :thinking:

Nov 2018
Dec 2019
Jan 2020
Feb 2020
…
.

#15

just need to group by year as well as month

1 Like
#16

Maybe you can group and filter the data client side with a dataview? If you don’t have like 10 thousands of records