Custom SQL query and Smart Number

Get Help
1

I really like theSmart Number feature which allows you to compare 2 metrics over the course of time. I’m wondering if there’s any way I can modify my custom SQL query to support Smart Numbers? If not is there another way I achieve something similar?

2

Hi @nickgi
Yes, you can use Smart Number with SQL. You just need to get at least two rows with one of the columns being a timeline.
Example for current simple Number query:

SELECT SUM(`total`) AS total FROM `table`

Smart Number:

SELECT DATE(`created`) AS 'period', SUM(`total`) AS 'sum' FROM `table` GROUP BY DATE(`created`)

Or just make create it in the Query Browser and click the View the SQL in upper right corner.

1 Like
3

This is great! One more question, how would I also include the use of a dashboard date range filter? This is what I currently have and it currently works when I’m not using the smart filter method. After changing to the smart filter query, the counts seem to be incorrect:

select sales_flat_quote.created_at, COUNT(DISTINCT(sales_flat_quote.entity_id))
FROM sales_flat_quote 
INNER JOIN sales_flat_quote_address ON sales_flat_quote_address.quote_id = sales_flat_quote.entity_id
INNER JOIN sales_flat_quote_shipping_rate ON sales_flat_quote_shipping_rate.address_id = sales_flat_quote_address.address_id
WHERE {{date_range}}
GROUP BY DATE(sales_flat_quote.created_at)

The {{date_range}} is a Field Filter that maps to sales_flat_quote.created_at.

4

That looks like Magento.
You can use the Query Browser to make the query and then View the SQL and make some small modifications.
I don’t know if you want to group by day, week, month - but which ever you want, you should match the column in both SELECT and GROUP BY. (EDIT: I see that I forgot that in the previous example)
And I’m not sure why you have the INNER JOINs, since it doesn’t look like you’re using them.

SELECT DATE(sales_flat_quote.created_at) AS 'period', COUNT(DISTINCT(sales_flat_quote.entity_id)) AS 'count'
FROM sales_flat_quote 
WHERE {{date_range}}
GROUP BY DATE(sales_flat_quote.created_at)
5

Yes it’s Magento. The reason for the inner join was that we’re trying to find all the quotes that have generated a shipping quote. We’re trying to group by week. After taking your advice and looking at some of the generated sql I came up with this that seems to be working:

select str_to_date(concat(yearweek(`sales_flat_quote`.`created_at`), ' Sunday'), '%X%V %W') AS 'period', COUNT(DISTINCT(sales_flat_quote.entity_id)) AS 'count'
FROM sales_flat_quote 
INNER JOIN sales_flat_quote_address ON sales_flat_quote_address.quote_id = sales_flat_quote.entity_id
INNER JOIN sales_flat_quote_shipping_rate ON sales_flat_quote_shipping_rate.address_id = sales_flat_quote_address.address_id
WHERE {{date_range}}
GROUP BY str_to_date(concat(yearweek(`sales_flat_quote`.`created_at`), ' Sunday'), '%X%V %W')
ORDER BY str_to_date(concat(yearweek(`sales_flat_quote`.`created_at`), ' Sunday'), '%X%V %W') ASC
6

Just as a clarification: smart scalars work on the result set and are more or less query-agnostic. The only constraint is that you need to have 2 columns in the resultset, one numeric and one date.

1 Like