#!/usr/bin/env python
# coding: utf-8

# ## Install required package
# Install ipython-sql to be able to run sql in Notebook

# In[1]:


get_ipython().run_line_magic('load_ext', 'sql')


# Connection String to your database.

# In[2]:


get_ipython().run_line_magic('sql', 'mysql+pymysql://root:1234@localhost/information_schema')


# In[5]:


get_ipython().run_cell_magic('sql', '', '\nselect pid, cid, sum(price) from sales.Transcations group by pid, cid with rollup;\n')


# In[ ]:


get_ipython().run_cell_magic('sql', '', '\nselect pid, cid, sum(price) from sales.Transcations group by pid, cid;\n')


# In[38]:


get_ipython().run_cell_magic('sql', '', '\nselect pid, cid, sum(price) from sales.Transcations group by cid, pid with rollup;\n')


# As Cube is not supported by mysql, we can simulate it as follows:

# In[40]:


get_ipython().run_cell_magic('sql', '', '\nselect pid, cid, sum(price) from sales.Transcations group by cid, pid with rollup\nunion \nselect pid, cid, sum(price) from sales.Transcations group by pid, cid with rollup\n')


# Use *Grouping()* --> 1 when grouping level or 0 (if not)

# In[6]:


get_ipython().run_cell_magic('sql', '', '\nselect pid, cid, sum(price), Grouping(pid), Grouping(cid) from sales.Transcations \ngroup by pid, cid with rollup;\n')


# In[ ]: