So I have this table resulting from a query. Is there a way to combine all of the purchases for the same username and order them in desc order to find the most loyal customers within the same query? maybe saving it to a variable and then doing something?

CodePudding user response：

This should do the work! You can read about CTE here - https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15.

    with users as ( 
    **YOUR QUERY HERE **
    ) 
    Select username, sum(number_of_purchase) from users 
    group by username

Example from Microsoft site:

-- Define the CTE expression name and column list.  
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)  
AS  
-- Define the CTE query.  
(  
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear  
    FROM Sales.SalesOrderHeader  
    WHERE SalesPersonID IS NOT NULL  
)  
-- Define the outer query referencing the CTE name.  
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear  
FROM Sales_CTE  
GROUP BY SalesYear, SalesPersonID  
ORDER BY SalesPersonID, SalesYear; 

CodePudding user response：

Depending on your MySql version. If prior to v8 then you can use a derived table, basically, wrapping your existing query as follows

Select username, sum(number_of_purchase) purchases
from (
  > Existing query <
)t
group by username
order by purchases desc

If you are using MySql 8 you could use window functions in your existing query to materialize the sum of purchases per user which you can then order by.

Without your existing query all I can do us suggest you incorporate the following, using the applicable column names

sum(purchase count column) over(partition by username) TotalPurchases