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.
CodePudding user response:
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
CodePudding user response:
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.