inserting into table then provide percent

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

inserting into table then provide percent

Author

Topic

Dmh188
Starting Member

37 Posts

Posted - 2011-04-29 : 13:29:42

Hi all, I am trying to place 2 results into a table and then provide the percentage between the 2.

select count(order_no)as free_ship from x_invoic
where item_code = 'sh'
and item_price = 0
and status = 9

select count(order_no) as total_ship from x_invoic
where item_code = 'sh'
and status = 9

I need the result to go into a table and then free_ship/tot_ship as percentage.

I have created a table with 3 columns and in the end, when i turn this into a stored procedure i will truncate the table. Only thing is i am not sure how to insert the results into the table and then fill in the third column with the percentage between those 2 values.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-29 : 13:41:22

;with cte1 as
(
select count(order_no)as free_ship
from x_invoic
where item_code = 'sh'
and item_price = 0
and status = 9
)
,cte2 as
(
select count(order_no) as total_ship
from x_invoic
where item_code = 'sh'
and status = 9
)
insert tbl
select c1.free_ship , c2.total_ship, 100.0*c1.free_ship/c2.total_ship
from cte1 c1
cross join cte2 c2

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-29 : 13:45:25

in fact probably

insert tbl
select free_ship , total_ship, 100.0*free_ship/total_ship
from
(
select
sum(case when status = 9 and item_price = 0 then 1 else 0 end) as free_ship ,
count(*) as total_ship
from x_invoic
where item_code = 'sh'
) a

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Dmh188
Starting Member

37 Posts

Posted - 2011-04-29 : 14:07:40

Thank you Nigel. The second one worked perfectly. Still working on perfecting nesting. much appreciated

Dmh188
Starting Member

37 Posts

Posted - 2011-04-29 : 14:24:10

Actually i spoke to soon.. When doing the COUNT it is counting everything, when it should only count where status = 9

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-29 : 14:37:26

So the status is always 9 for both count? If so, change the derived table to

select
sum(case when item_price = 0 then 1 else 0 end) as free_ship ,
count(*) as total_ship
from x_invoic
where item_code = 'sh' and status = 9

Jim

Everyday I learn something that somebody else already knew

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-29 : 14:52:18

sorry didn't realise it was just the item price that distinguishes the values.
Would have thought you could make this change yourself.

insert tbl
select free_ship , total_ship, 100.0*free_ship/total_ship
from
(
select
sum(case when item_price = 0 then 1 else 0 end) as free_ship ,
count(*) as total_ship
from x_invoic
where item_code = 'sh'
and status = 9
) a

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources