CODEWITHSUNDEEP
mysql
Ben Simmons
Ben Simmons

Reputation: 329

Add Percentage after Sorting by Year and Quarter MySQL

The following query returns order totals for the Year and Quarter. I would also like to have the value stated as a percentage of the total spend.

SELECT DATABASE() AS PURCHASING_Total_PO_Value_By_Year, 
YEAR(order_headers.created_at) AS Year, 
QUARTER(order_headers.created_at) AS Quarter, 
COUNT(DISTINCT(order_headers.id)) AS Orders, 
SUM(order_lines.accounting_total) AS Order_Total 
FROM (order_lines LEFT JOIN order_headers ON order_lines.order_header_id = order_headers.id) 
WHERE (order_headers.status LIKE 'Issued') OR (order_headers.status LIKE 'Closed') 
Group by YEAR(order_headers.created_at), QUARTER(order_headers.created_at)

I need to take SUM(order_lines.accounting_total) as 'TOTAL SPEND' without applying the year and quarter filter.

Any pointers/tips would be appreciated.

Current Query Returns

Upvotes: 0

Views: 52

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521534

If you really just want the grand total of the Order_Total with no year or quarter aggregation, then you could try using a subquery:

SELECT DATABASE() AS PURCHASING_Total_PO_Value_By_Year, 
    YEAR(order_headers.created_at) AS Year, 
    QUARTER(order_headers.created_at) AS Quarter, 
    COUNT(DISTINCT(order_headers.id)) AS Orders, 
    SUM(order_lines.accounting_total) AS Order_Total,
    100 * SUM(order_lines.accounting_total) /
        (SELECT SUM(accounting_total) FROM order_lists) AS Order_Percentage
FROM order_lines LEFT JOIN order_headers
    ON order_lines.order_header_id = order_headers.id
WHERE (order_headers.status LIKE 'Issued') OR (order_headers.status LIKE 'Closed')
GROUP BY YEAR(order_headers.created_at), QUARTER(order_headers.created_at)

The column I added to your SELECT is this:

100 * SUM(order_lines.accounting_total) /
    (SELECT SUM(accounting_total) FROM order_lists) AS Order_Percentage

Upvotes: 2

Related Questions