How to find fulfillment_summary in SQLite. How to find order % of total orders

Question

This is my QUERY in SQLite. Currently following the Google Data Analytics Certificate.

SELECT
    Warehouse.warehouse_id,
    Warehouse.state || ': ' || Warehouse.warehouse_alias AS warehouse_name,
    COUNT(Orders.order_id) AS number_of_orders,
    (SELECT
        COUNT(*)
        FROM Warehouse_Orders Orders)
    AS total_orders,
    CASE
        WHEN COUNT(Orders.order_id)/(SELECT COUNT(*) FROM Warehouse_Orders Orders) <= 0.20
        THEN 'Fulfilled 0-20% of Orders'
        WHEN COUNT(Orders.order_id)/(SELECT COUNT(*) FROM Warehouse_Orders Orders) > 0.20
        AND COUNT(Orders.order_id)/(SELECT COUNT(*) FROM Warehouse_Orders Orders) <= 0.60
        THEN 'Fulfilled 21-60% of Orders'
        ELSE 'Fulfilled more than 60% of Orders'
        END AS fulfillment_summary
FROM Warehouse_Stats Warehouse
LEFT JOIN Warehouse_Orders Orders
ON Warehouse.warehouse_id = Orders.warehouse_id
GROUP BY
    Warehouse.warehouse_id,
    warehouse_name
HAVING
    COUNT(Orders.order_id) >1

I realise it is a problem with my CASE

The Orders.order_id is fine as the COUNT returns the number of orders per warehouse. -Yet the total is not working "SELECT COUNT(*) FROM Warehouse_Orders Orders"

How do I find the total?

I've tried, to take my SQL query apart and try different methods but I'm struggling to return the total.

score 0 · Answer 1 · answered Oct 31 '22 at 19:37

You should try to avoid running the same query multiple times. For more complex analysis you often need to think in steps: get this result set and query that for the next step. You can do this using a derived table.

Also note you should convert integers to decimal numbers before devision and comparison to a another decimal. Does it work for you?

SELECT *, 
    CASE WHEN 
        (CAST(total_orders as REAL)/CAST(number_of_orders as REAL)) <= 0.2
        THEN 'Fulfilled 0-20% of Orders'
        WHEN 
        (CAST(total_orders as REAL)/CAST(number_of_orders as REAL)) BETWEEN 0.2 and 0.6
        THEN 'Fulfilled 21-60% of Orders'
        ELSE 'Fulfilled more than 60% of Orders'
    END AS fulfillment_summary
FROM 
(
    SELECT
        Warehouse.warehouse_id,
        Warehouse.state || ': ' || Warehouse.warehouse_alias AS warehouse_name,
        COUNT(Orders.order_id) AS number_of_orders,
        (SELECT COUNT(*) FROM Warehouse_Orders Orders) AS total_orders
    FROM Warehouse_Stats Warehouse
    LEFT JOIN Warehouse_Orders Orders ON Warehouse.warehouse_id = Orders.warehouse_id
    GROUP BY Warehouse.warehouse_id, warehouse_name
    HAVING COUNT(Orders.order_id) >1
) totals

Hi thanks, I tried to run your query and it made all values be retrieved as over 60%. The solution to this after a lot of searching was for me to times each count in the case statement by 1.0. This is my updated version below :). *** CASE WHEN 1.0 * COUNT(Orders.order_id)/(SELECT COUNT(*) FROM Warehouse_Orders Orders) <= 0.20 **** and so on — Edward, Oct 31 '22 at 19:46
Great! Yeah, conversions and operations usually need a little tuning. Please update the query in the answer for future reference! — Gerballi, Oct 31 '22 at 20:14

score 0 · Answer 2 · answered Nov 01 '22 at 11:24

UPDATED VERSION:

So after a lot of searching on the Web, I realised that while using SQLite you have to convert numbers before looking to make percentages out of them.

This is the end result of the query that worked for me.

SELECT
    Warehouse.warehouse_id,
    Warehouse.state || ': ' || Warehouse.warehouse_alias AS warehouse_name,
    COUNT(Orders.order_id) AS number_of_orders,
    (SELECT
        COUNT(*)
        FROM Warehouse_Orders Orders)
    AS total_orders,
    CASE
        WHEN 1.0 * COUNT(Orders.order_id)/(SELECT COUNT(*) FROM Warehouse_Orders) <= 0.20
        THEN 'Fulfilled 0-20% of Orders'
        WHEN 1.0 * COUNT(Orders.order_id)/(SELECT COUNT(*) FROM Warehouse_Orders) > 0.20
        AND 1.0 * COUNT(Orders.order_id)/(SELECT COUNT(*) FROM Warehouse_Orders) <= 0.60
        THEN 'Fulfilled 21-60% of Orders'
        ELSE 'Fulfilled more than 60% of Orders'
        END AS fulfillment_summary
FROM Warehouse_Stats Warehouse
LEFT JOIN Warehouse_Orders Orders
ON Warehouse.warehouse_id = Orders.warehouse_id
GROUP BY
    Warehouse.warehouse_id,
    warehouse_name
HAVING
    COUNT(Orders.order_id) >1

As you can see I input 1.0 * ... For each COUNT of the individual orders. Which ended up making the percentages work.

How to find fulfillment_summary in SQLite. How to find order % of total orders

2 Answers2