0

I have these tables and I want to show the percantege of customer with deliverd to pending
and the in another sql statment show the number of customer with deliverd to pending
I'm not sure how to calulate these two. I tried the build query on APEX , but it not helping
customer
c_id NUMBER
c_name VARCHAR2
c_address VARCHAR2
order
o_id NUMBER
fk_c_id NUMBER
o_name VARCHAR2
o_price NUMBER
o_orderDate DATE
order_history
oh_id
fk_o_id
fk_c_id
oh_status VARCHAR2 // status can be pending , deliverd or approved
oh_dateChanged DATE

Edited by sk8ergirl

2
Contributors
1
Reply
11
Views
3 Years
Discussion Span
Last Post by rch1231
0

Hello,

What you need to do is create a couple of sub queries and then combine them for the final answer. Here is an example from the OReilly MySQL Cookbook that does something like what you want.

The percentages represent the ratio of each driver's miles to the total miles for all drivers. To perform the percentage calculation, you need a per-group summary to get each driver's miles and also an overall summary to get the total miles. First, run a query to get the overall mileage total:

mysql> SELECT @total := SUM(miles) AS 'total miles' FROM driver_log;
+-------------+
| total miles |
+-------------+
|        2166 |
+-------------+



Now, calculate the per-group values and use the overall total to compute the percentages:

mysql> SELECT name,
    -> SUM(miles) AS 'miles/driver',
    -> (SUM(miles)*100)/@total AS 'percent of total miles'
    -> FROM driver_log GROUP BY name;
+-------+--------------+------------------------+
| name  | miles/driver | percent of total miles |
+-------+--------------+------------------------+
| Ben   |          362 |                16.7128 |
| Henry |          911 |                42.0591 |
| Suzi  |          893 |                41.2281 |
+-------+--------------+------------------------+



To combine the two statements into one, use a subquery that computes the total miles:

mysql> SELECT name,
    -> SUM(miles) AS 'miles/driver',
    -> (SUM(miles)*100)/(SELECT SUM(miles) FROM driver_log)
    ->   AS 'percent of total miles'
    -> FROM driver_log GROUP BY name;
+-------+--------------+------------------------+
| name  | miles/driver | percent of total miles |
+-------+--------------+------------------------+
| Ben   |          362 |                16.7128 |
| Henry |          911 |                42.0591 |
| Suzi  |          893 |                41.2281 |
+-------+--------------+------------------------+
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.