Advanced Queries

We have shown you how easy SQL can be, and hopefully we have shown that with a little patience you can achieve quite a lot. We will now briefly show you how powerful SQL can be. We won't attempt to explain how all these examples work in detail. If you are new to SQL it will be a while before you can write queries like these, but we hope they will give you an idea of what is possible.

Example 7

We have a Joomla extension that creates a row in a table for each sale we make. We want to build a bar chart of sales by date. As you will see later, a bar chart requires a text column for the bar labels and a numeric column for the bar height. The dates in our table are stored as Unix Date-Time. We need SQL to calculate the total amount for an item, for each date within a date range. Here's the solution:

SELECT DATE_FORMAT(DATE(FROM_UNIXTIME(payment_date)),"%d") AS pay_date,
SUM(price) AS total_price,
DATE(FROM_UNIXTIME(payment_date)) AS sql_date
FROM #__my_sales
WHERE item_number = 1
AND (DATE(FROM_UNIXTIME(payment_date))
BETWEEN DATE("2020-01-01") AND DATE ("2020-03-01"))
GROUP BY sql_date

The GROUP BY clause here takes the rows for each date and lets you apply "aggregate" functions like SUM to them. Here, we used SUM to get the total sales for each date. The extra column (sql_date) is created purely for use by the GROUP BY clause. Plotalot will use the first two columns to create the chart, and ignore any extra columns.

Example 8

We want the article hits pie chart to include a slice for all the "Other" articles not in the top ten:

SELECT * FROM
((SELECT title, hits
FROM #__content ORDER BY hits DESC LIMIT 10) AS temp1)
UNION
(SELECT 'Other' AS title, SUM(hits)
FROM (SELECT title, hits FROM #__content ORDER BY hits DESC
LIMIT 10,18446744073709551615) AS temp2)

The first sub-query gets the top ten articles as before. UNION tells SQL to add two result sets together. The second sub-query gets the sum of the hits for the articles not in the top ten. Try it!

Example 9

Here's a table containing only one column (called "data"), with the data as name=value pairs. The first four rows of the table look like this:

We need a line graph of the x values against the y values. Here's the solution:

SELECT CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(data, 'x_value=', -1), '||', 1)
AS SIGNED INTEGER) AS x,
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(data, 'y_value=', -1), '||', 1)
AS SIGNED INTEGER) AS y
FROM #__name_value_data ORDER BY `y`

Which returns this result set: