Below query calculates running sum and running average of book sales, along with a cumulative data items.
the query cross-joins two instaces of table titles, grouping the result by the first-table(t1) title IDs and limiting the second table(tw) rows to ID values smaller than or equal to the t1 row to which they're joined.
table: titles
After cross join (titles)t1 and (titles)t2
Query:
SELECT
t1.ID,sum(t2.sales),avg(t2.sales),count(t2.sales)
FROM titles t1,titiles t2
WHERE t1.sales >= t2.sales
GROUP BY t1.ID
Result:
the query cross-joins two instaces of table titles, grouping the result by the first-table(t1) title IDs and limiting the second table(tw) rows to ID values smaller than or equal to the t1 row to which they're joined.
table: titles
| t1.id | t1.sales |
| t01 | 566 |
| t02 | 9566 |
| t03 | 25667 |
| t04 | 13001 |
After cross join (titles)t1 and (titles)t2
| t1.id | t1.sales | t2.id | t2.sales |
| t01 | 566 | t01 | 566 |
| t02 | 9566 | t01 | 566 |
| t02 | 9566 | t02 | 9566 |
| t03 | 25667 | t01 | 566 |
| t03 | 25667 | t02 | 9566 |
| t03 | 25667 | t03 | 25667 |
| t04 | 13001 | t01 | 566 |
| t04 | 13001 | t02 | 9566 |
| t04 | 13001 | t03 | 25667 |
| t04 | 13001 | t04 | 13001 |
Query:
SELECT
t1.ID,sum(t2.sales),avg(t2.sales),count(t2.sales)
FROM titles t1,titiles t2
WHERE t1.sales >= t2.sales
GROUP BY t1.ID
Result:
| ID | SUM(T2.SALES) | AVG(T2.SALES) | COUNT(T2.SALES) |
| 1 | 566 | 566 | 1 |
| 2 | 10132 | 5066 | 2 |
| 3 | 48800 | 12200 | 4 |
| 4 | 23133 | 7711 | 3 |
No comments:
Post a Comment