CODEWITHSUNDEEP
sqlsql-server
Goolsy
Goolsy

Reputation: 237

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

Upvotes: 0

Views: 56

Answers (3)

Bhavika Zimbar
Bhavika Zimbar

Reputation: 481

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

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

I used a window function inside a subquery to get your desired output. The output should be ordered by both the store name record count and the date of the transaction.

SELECT t.date, t.storeName, t.amount
FROM
(
    SELECT date, storeName, amount,
        COUNT(*) OVER(PARTITION BY storeName ORDER BY date) AS storeCount
    FROM yourTable
) t
ORDER BY t.storeCount DESC, t.date

Upvotes: 3

Squirrel
Squirrel

Reputation: 24763

use count with window function to count the number of rows by store

SELECT *, store_count = count(*) over (partition by Store)
FROM   yourtalbe
ORDER BY store_count desc

Upvotes: 1

Related Questions