Use of CONCAT FLOOR Statement

Question

I need to create a frequency table that displays the revenue per bin. The bin has a range of 500. The code shown below almost do the work except it is displaying a different values:

SELECT IF(rng='1 - 500','0 - 500',rng)AS Revenue,
       IFNULL(B.rngcount,0)AS Count 
FROM
    (
    SELECT '1 - 500' rng UNION
    SELECT '501 - 1000'   UNION
    SELECT '1001 - 1500'  UNION
    SELECT '1501 - 2000'  UNION
    SELECT '2001 - 2500'
   ) A 
LEFT JOIN (SELECT CONCAT(FLOOR((product.price * line_item.quantity)/500)*500+1,' - ',FLOOR((product.price * line_item.quantity)/500)*500+500) rng, 
                  COUNT(1) rngcount 
           FROM  line_item, product 
           GROUP BY rng) B USING (rng);

Answer 1

SELECT CONCAT(ranges.lo, ' - ', ranges.hi) Revenue,
       COUNT(*) `Count`
FROM line_item
JOIN product USING (product_id)
JOIN ( SELECT    1 lo,  500 hi UNION
       SELECT  501   , 1000    UNION
       SELECT 1001   , 1500    UNION
       SELECT 1501   , 2000    UNION
       SELECT 2001   , 2500 ) ranges ON product.price * line_item.quantity BETWEEN ranges.lo AND ranges.hi
GROUP BY ranges.lo, ranges.hi;

fiddle

---

As I have commented - the relation between product and line_item must be specified. Common column for them is product_id - so according joining condition added.

And ranges table is used in a form from-to, this simplifies checking what range the revenie is posessed in.

---

is it possible to sum the Count column, and place the total.

Easily.

SELECT CONCAT(ranges.lo, ' - ', ranges.hi) Revenue,
       COUNT(*) `Count`
FROM line_item
JOIN product USING (product_id)
JOIN ( SELECT    1 lo, 500 hi UNION
       SELECT  501   , 1000   UNION
       SELECT 1001   , 1500   UNION
       SELECT 1501   , 2000   UNION
       SELECT 2001   , 2500 ) ranges ON product.price * line_item.quantity BETWEEN ranges.lo AND ranges.hi
GROUP BY Revenue WITH ROLLUP;

Totals row is the last, with NULL value in Revenue column.

Answer 2

Here is the code I made to group the revenue according to week.Maybe you can still simplify this.

    (sales_agent.name)AS Agent, 
    (sales_team.name)AS Team, 
    
    SUM(IF((WEEK(order_.date) - WEEK(DATE_SUB(order_.date, INTERVAL DAYOFMONTH(order_.date)-1 DAY)) + 1=1), product.price * line_item.quantity,0)) AS Week_1, 
    SUM(IF((WEEK(order_.date) - WEEK(DATE_SUB(order_.date, INTERVAL DAYOFMONTH(order_.date)-1 DAY)) + 1=2), product.price * line_item.quantity,0)) AS Week_2, 
    SUM(IF((WEEK(order_.date) - WEEK(DATE_SUB(order_.date, INTERVAL DAYOFMONTH(order_.date)-1 DAY)) + 1=3), product.price * line_item.quantity,0)) AS Week_3,
    SUM(IF((WEEK(order_.date) - WEEK(DATE_SUB(order_.date, INTERVAL DAYOFMONTH(order_.date)-1 DAY)) + 1=4), product.price * line_item.quantity,0)) AS Week_4,
    SUM(IF((WEEK(order_.date) - WEEK(DATE_SUB(order_.date, INTERVAL DAYOFMONTH(order_.date)-1 DAY)) + 1=5), product.price * line_item.quantity,0)) AS Week_5,
    SUM(product.price * line_item.quantity) AS Total
FROM ((((line_item
INNER JOIN order_ ON line_item.order_id = order_.order_id)
INNER JOIN sales_agent ON order_.agent_id = sales_agent.agent_id)
INNER JOIN sales_team ON sales_agent.team_id  = sales_team.team_id)
INNER JOIN product ON line_item.product_id = product.product_id)
GROUP BY sales_agent.agent_id;