CODEWITHSUNDEEP
sql-servergroup-bysql-order-by
user2008865
user2008865

Reputation: 61

Group By with Ordering

im looking for a little help with an SQL query, I've been racking my brains for a few hours.

I have some data within an MSSQL table that looks like the following:

price, date
15.98, 2014-05-31 13:24:05.287
15.98, 2014-06-01 01:24:15.250
15.98, 2014-06-01 13:24:19.280
15.98, 2014-06-02 01:25:01.680
15.98, 2014-06-02 13:25:10.477
9.99, 2014-06-03 01:25:12.580
15.98   2014-06-03 13:26:01.930
15.98   2014-06-04 01:26:11.423
9.99    2014-06-04 13:26:17.853
9.99    2014-06-05 01:27:01.800

I would like to return the count of each price, however not grouping all prices together, I would like to group based on time too. The result I would like to return is:

price, count
15.98, 5
9.99, 1
15.98, 2
9.99, 2

How would I go about manipulating the GROUP BY clause to produce the above set?

Upvotes: 0

Views: 62

Answers (1)

peterm
peterm

Reputation: 92785

This (grouping of continuous ranges) is called gaps-and-islands problem and can be effectively solved by using analytic functions (specifically ROW_NUMBER()) 

SELECT price, COUNT(*) count
  FROM
(
  SELECT price, date, 
         ROW_NUMBER() OVER (ORDER BY date) rnum,
         ROW_NUMBER() OVER (PARTITION BY price ORDER BY date) rnum2
    FROM table1 
) q
 GROUP BY price, rnum - rnum2
 ORDER BY MIN(rnum)

Output:

| PRICE | COUNT |
|-------|-------|
| 15.98 |     5 |
|  9.99 |     1 |
| 15.98 |     2 |
|  9.99 |     2 |

Here is SQLFiddle demo

Upvotes: 1

Related Questions