user2430812 user2430812 - 4 months ago 19
SQL Question

Sort as a result of another Grouping

I need to sort some results based on the grouping of another query.

I have a table with transactions and one of the fields is the store. I want to return all transactions ordered by transaction date but within that I need to have the transactions belonging to the store with the most transactions listed first.

For example:



Date1, Store A, Amount
Date1, Store B, Amount
Date2, Store A, Amount
Date3, Store A, Amount
Date3, Store B, Amount
Date3, Store B, Amount
Date4, Store B, Amount
Date5, Store B, Amount

Has to be returned as:

Date1, Store B, Amount
Date3, Store B, Amount
Date3, Store B, Amount
Date4, Store B, Amount
Date5, Store B, Amount
Date1, Store A, Amount
Date2, Store A, Amount
Date3, Store A, Amount



Because Store B has more transactions

Answer

As per your given details, Please check the output:

--table scripts
    CREATE TABLE Store_Data 
   (datevalue datetime, storeinfo nvarchar(40), Amount numeric(18,2))


    INSERT INTO Store_Data
    SELECT '2016-07-16 10:54:33.020','Store B' , 16000
    UNION ALL
    SELECT '2016-07-18 10:54:33.020','Store A' , 15000
    UNION ALL
    SELECT '2016-07-28 10:54:33.020','Store B' , 10800
    UNION ALL
    SELECT '2016-07-20 10:54:33.020','Store A' , 9000
    UNION ALL
    SELECT '2016-07-23 10:54:33.020','Store B' , 1000
    UNION ALL
    SELECT '2016-07-22 10:54:33.020','Store B' , 7000
    UNION ALL
    SELECT '2016-07-08 10:54:33.020','Store B' , 1000
    UNION ALL
    SELECT '2016-07-12 10:54:33.020','Store A' , 1000
    UNION ALL        
    SELECT '2016-07-15 10:54:33.020','Store A' , 11000
    UNION ALL        
    SELECT '2016-07-18 10:54:33.020','Store B' , 1000
    UNION ALL        
    SELECT '2016-07-02 10:54:33.020','Store A' , 5000
    UNION ALL        
    SELECT '2016-07-24 10:54:33.020','Store B' , 1000
    UNION ALL        
    SELECT '2016-07-08 10:54:33.020','Store A' , 100000
    UNION ALL        
    SELECT '2016-07-23 10:54:33.020','Store B' , 5000
    UNION ALL        
    SELECT '2016-07-18 10:54:33.020','Store B' , 10000

    -

--final query

SELECT a.datevalue,
       a.storeinfo,
       a.Amount 
FROM Store_Data AS a 
INNER JOIN
(
    SELECT storeinfo,
           COUNT(1) AS TotalTrans 
    FROM Store_Data
    GROUP BY storeinfo
) AS b
    ON b.storeinfo = a.storeinfo
ORDER BY b.TotalTrans DESC,
         a.datevalue
Comments