MariaDB Grouping

MariaDB Grouping

(jlive)[crashcourse]>SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id WITH ROLLUP;

+---------+-----------+

| vend_id | num_prods |

+---------+-----------+

|    1001 |         3 |

|    1002 |         2 |

|    1003 |         7 |

|    1005 |         2 |

|    NULL |        14 |

+---------+-----------+

5 rows in set (0.00 sec)

(jlive)[crashcourse]>SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id HAVING COUNT(*) >= 2;

+---------+-----------+

| vend_id | num_prods |

+---------+-----------+

|    1001 |         3 |

|    1002 |         2 |

|    1003 |         7 |

|    1005 |         2 |

+---------+-----------+

 

4 rows in set (0.00 sec)

(jlive)[crashcourse]>SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price >= 10 GROUP BY vend_id HAVING COUNT(*) >= 2;

+---------+-----------+

| vend_id | num_prods |

+---------+-----------+

|    1003 |         4 |

|    1005 |         2 |

+---------+-----------+

 

2 rows in set (0.00 sec)

(jlive)[crashcourse]>SELECT order_num, SUM(quantity*item_price) ordertotal FROM orderitems GROUP BY order_num HAVING ordertotal >= 50 ORDER BY 2 DESC;

+-----------+------------+

| order_num | ordertotal |

+-----------+------------+

|     20007 |    1000.00 |

|     20005 |     149.87 |

|     20008 |     125.00 |

|     20006 |      55.00 |

+-----------+------------+

 

4 rows in set (0.00 sec)

  

(jlive)[crashcourse]>SELECT order_num, SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price) >= 50 ORDER BY ordertotal DESC LIMIT 2 OFFSET 1;

+-----------+------------+

| order_num | ordertotal |

+-----------+------------+

|     20005 |     149.87 |

|     20008 |     125.00 |

+-----------+------------+

 

2 rows in set (0.00 sec)