Install ipython-sql to be able to run sql in Notebook
%load_ext sql
Connection String to your database.
%sql mysql+pymysql://root:1234@localhost/information_schema
%%sql
select pid, cid, sum(price) from sales.Transcations group by pid, cid with rollup;
* mysql+pymysql://root:***@localhost/information_schema 10 rows affected.
| pid | cid | sum(price) |
|---|---|---|
| 1 | 1 | 20.00 |
| 1 | 2 | 10.00 |
| 1 | 3 | 15.00 |
| 1 | None | 45.00 |
| 2 | 1 | 10.00 |
| 2 | 2 | 30.00 |
| 2 | 3 | 15.00 |
| 2 | 4 | 30.00 |
| 2 | None | 85.00 |
| None | None | 130.00 |
%%sql
select pid, cid, sum(price) from sales.Transcations group by pid, cid;
%%sql
select pid, cid, sum(price) from sales.Transcations group by cid, pid with rollup;
* mysql+pymysql://root:***@localhost/information_schema mysql+pymysql://root:***@localhost/movies 12 rows affected.
| pid | cid | sum(price) |
|---|---|---|
| 1 | 1 | 20.00 |
| 2 | 1 | 10.00 |
| None | 1 | 30.00 |
| 1 | 2 | 10.00 |
| 2 | 2 | 30.00 |
| None | 2 | 40.00 |
| 1 | 3 | 15.00 |
| 2 | 3 | 15.00 |
| None | 3 | 30.00 |
| 2 | 4 | 30.00 |
| None | 4 | 30.00 |
| None | None | 130.00 |
As Cube is not supported by mysql, we can simulate it as follows:
%%sql
select pid, cid, sum(price) from sales.Transcations group by cid, pid with rollup
union
select pid, cid, sum(price) from sales.Transcations group by pid, cid with rollup
* mysql+pymysql://root:***@localhost/information_schema mysql+pymysql://root:***@localhost/movies 14 rows affected.
| pid | cid | sum(price) |
|---|---|---|
| 1 | 1 | 20.00 |
| 2 | 1 | 10.00 |
| None | 1 | 30.00 |
| 1 | 2 | 10.00 |
| 2 | 2 | 30.00 |
| None | 2 | 40.00 |
| 1 | 3 | 15.00 |
| 2 | 3 | 15.00 |
| None | 3 | 30.00 |
| 2 | 4 | 30.00 |
| None | 4 | 30.00 |
| None | None | 130.00 |
| 1 | None | 45.00 |
| 2 | None | 85.00 |
Use Grouping() --> 1 when grouping level or 0 (if not)
%%sql
select pid, cid, sum(price), Grouping(pid), Grouping(cid) from sales.Transcations
group by pid, cid with rollup;
* mysql+pymysql://root:***@localhost/information_schema 10 rows affected.
| pid | cid | sum(price) | Grouping(pid) | Grouping(cid) |
|---|---|---|---|---|
| 1 | 1 | 20.00 | 0 | 0 |
| 1 | 2 | 10.00 | 0 | 0 |
| 1 | 3 | 15.00 | 0 | 0 |
| 1 | None | 45.00 | 0 | 1 |
| 2 | 1 | 10.00 | 0 | 0 |
| 2 | 2 | 30.00 | 0 | 0 |
| 2 | 3 | 15.00 | 0 | 0 |
| 2 | 4 | 30.00 | 0 | 0 |
| 2 | None | 85.00 | 0 | 1 |
| None | None | 130.00 | 1 | 1 |