Piechart MySQL query help

[chrisw]

Hey guys and gals,

I have been asked to produce a piechart on the dashboard which shows 2 values. Where date is either side of a set value (20 minutes).

SELECT COUNT(*)  
	FROM `tableName` 
	WHERE datecolumn > DATE_SUB(NOW(),INTERVAL 20 MINUTE) 

SELECT COUNT(*)  
	FROM `tableName` 
	WHERE datecolumn  < DATE_SUB(NOW(),INTERVAL 20 MINUTE) 

I can get this into a table as follows, but can work out how to make this work with the pie chart:
SELECT
NOW() AS time_sec,
COUNT() AS “Value1”,
(SELECT COUNT()
FROM tableName
WHERE datecolumn > DATE_SUB(NOW(),INTERVAL 20 MINUTE) AS “Value2”

FROM 
  tableName
WHERE datecolumn  < DATE_SUB(NOW(),INTERVAL 20 MINUTE) 

Can anyone advise? I am sure with Grafana this should be easier since I am using time data.

[chrisw]

My last query does work, I forgot to change it to timeseries

[yuling565]

Hi. I used your last query, but I didn’t have a pie chart. Can you help me see what is going on?

[alexandrefrp]

Pie Charts i think who only works with Time series in “Format as”, add in select some collum with datetime and rename to times and use group by.

ie:
SELECT __timeGroup([data], '60m') as time, motivo as metric, SUM(qtde) AS QTDE FROM MyTable WHERE [data] BETWEEN __timeFrom() AND __timeTo() GROUP BY __timeGroup([data], ‘60m’), [motivo]

[yuling565]

Can you show me the details of your pie chart settings?

[alexandrefrp]

Ok, see