How to get calculated data from one column in database-CodePudding

im new in sql. I cannot get data with format what i want in one step. Now i'm using more sql commands. I want to get all data in one command because i cant to connect them in subquery with group by. Somebodys can help me?

example of Table i have:

id	order_id	order_status
1	1	0
2	1	0
3	1	0
4	1	1
5	1	1
6	2	0
7	2	0
8	2	1

Table i want to have after sql query:

order_id	count	of	progress(%)
1	2	5	40
2	1	3	33

queries i use:

SELECT order_id, COUNT(status) as count
FROM `orders`
WHERE status = 1
GROUP by order_id;

SELECT order_id, COUNT(status) as of
FROM `orders`
GROUP by order_id;

SELECT order_id,
       CAST((SELECT COUNT(status) FROM `orders` WHERE status = 1) /
            (SELECT COUNT(status) FROM `orders`) *100 as int) AS progress FROM orders
group by order_id;

but last working properly only if i use where to single order id.

I want to make this data in one sql query to format i showed up.

Thanks a lot guys!

CodePudding user response：

You don't need subqueries to do this, SQL's ordinary aggregate functions already work as you want with your group by clause:

SELECT order_id,
       SUM(order_status) AS `count`,
       COUNT(*) AS `of`,
       SUM(order_status) / COUNT(order_status) * 100 as `progress`
FROM orders
group by order_id;

See example at http://sqlfiddle.com/#!9/d1799db/4/0

CodePudding user response：

you need to use multiple subqueries here's a query that I used and worked on your example on the onecompiler.com website

-- create
CREATE TABLE EMPLOYEE (
  order_id INTEGER,
  order_status INTEGER
);

-- insert
INSERT INTO EMPLOYEE VALUES (1,0 );
INSERT INTO EMPLOYEE VALUES (1, 0);
INSERT INTO EMPLOYEE VALUES (1, 0);


INSERT INTO EMPLOYEE VALUES (1, 1);
INSERT INTO EMPLOYEE VALUES (1,1 );


INSERT INTO EMPLOYEE VALUES (2, 0);
INSERT INTO EMPLOYEE VALUES (2, 0);
INSERT INTO EMPLOYEE VALUES (2, 1);

select *
from EMPLOYEE;

SELECT order_id, count, off , count/off
from(
    select distinct order_id as order_id, 
    (select count(order_id) from EMPLOYEE C WHERE A.order_id=C.order_id  AND order_status =1)  as 'count',
    (select count(order_id) from EMPLOYEE B WHERE A.order_id=B.order_id )  as 'off'
    FROM EMPLOYEE A
    ) AA
    ;

CodePudding user response：

You need to use sum and count with group by.

create table orders(
id int,   
order_id int, 
order_status int);

insert into orders values
(1,1,0),
(2,1,0),
(3,1,0),
(4,1,1),
(5,1,1),
(6,2,0),
(7,2,0),
(8,2,1);

select
order_id, 
sum(order_status) count,
count(order_id) "of",
(100 * sum(order_status))
/ count(order_id) progress
from orders
group by order_id
order by order_id;

order_id | count | of | progress
-------: | ----: | -: | -------:
       1 |     2 |  5 |  40.0000
       2 |     1 |  3 |  33.3333

db<>fiddle here

CodePudding user response：

i was described my problem without some details, w i want to join with other table but i see only record with status

oders_details | id | order_describe | order_date | |:----:|:--------------:|:----------:| | 1 | sample 1 | 2022-02-28 | | 2 | sample 2 | 2022-02-28 | | 3 | sample 3 | 2022-03-01 | | 4 | sample 4 | 2022-03-02 |

orders_status | id | order_id |order_status| |:---:|:---------------:|:----------:| | 1 | 1 | 0 | | 2 | 1 | 0 | | 3 | 1 | 0 | | 4 | 1 | 1 | | 5 | 1 | 1 | | 6 | 2 | 0 | | 7 | 2 | 0 | | 8 | 2 | 1 |

table i want after query

orders_view | id |order_id|order_describe| order_date | count | of | progress | |-----|--------|--------------|------------|-------|----|:--------:| | 1 | 1 | sample 1 | 2022-02-28| 2 | 5 | 40 | | 2 | 2 | sample 2 | 2022-02-28| 1 | 3 | 33 | | 3 | 3 | sample 3 | 2022-03-01| null |null| null | | 4 | 4 | sample 4 | 2022-03-02| null |null| null |

i want to get some hint what i have todo, to get finally table or view, not complete solution, to better understand sql lang