question

sqlnewb avatar image
sqlnewb asked

Divide a Query

Hi, I am trying to write an anonymous block that divides a query by all the rows in the table to get the percentage. For example I have a query: `select * from tableA where productX IN ('Z','T','M') AND discount='TRUE';` Say this returns 10,000 rows The whole table is 20,000 rows. So I want to have it show 50%. I first just wrote the query as is and divided the rows by each other but I am thinking it would be easier not to HARD CODE the total row numbers instead using a SELECT COUNT...INTO a count variable. This way I can just run the anonymous block if rows are added to the table. Not sure how to go about this. Any advice? EDIT Declare v_rec1 NUMBER; v_rec2 NUMBER; v_totrow Number; BEGIN set v_totrow = count(*) from tableA /*Calculate for record 1 */ SELECT (*/v_totrow) as Percent INTO v_rec1 FROM tableA WHERE productA IN ('C','O','M') and rec_number = 1 ; DBMS_OUTPUT.PUT_LINE('This is the percent of record 1 '||v_rec1); /*Calculate for record 2 */ SELECT (*/v_totrow) as Percent INTO v_rec1 FROM tableA WHERE productA IN IN ('C','O','M') and rec_number = 2 ; DBMS_OUTPUT.PUT_LINE('This is the percent of record 1 '||v_rec2); SELECT avg(v_rec1, v_rec2) as Total_Percent; DBMS_OUTPUT.PUT_LINE('This is the average of both records '||v_totrow); END ; / show errors I want to take percent change for each rec_number and then the total average of both rec_number 1 and rec_number 2. I am using Oracle. I am having issue calculating the row number to divide by?
stored-proceduresoraclerow-counts
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sqlnewb avatar image
sqlnewb answered
Thanks figured it out. Was overthinking a simple problem. Appreciate all the help! set serveroutput on declare v_rec1 INT; v_totrow INT; v_total number; BEGIN SELECT COUNT(*) INTO v_totrow FROM tableA; SELECT COUNT (*) INTO v_rec1 FROM supplied_data where product IN ('C','O','M'); v_total := ((v_rec1/v_totrow) * 1.0); DBMS_OUTPUT.PUT_LINE('This is the average '||v_total); END; / show errors
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Often the case. Glad you got it sorted.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
Here's an example using sys.objects from a test database on my machine declare @rowcount int declare @totalrows int select @totalrows = count(*) from sys.objects select * from sys.objects where type ='U' set @rowcount = @@rowcount select @rowcount, @totalrows, @rowcount/(@totalrows * 1.0)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I added in my code above. I am still having issue decalring the totalrows to divide by
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
Taking @Kev Riley's example a stage further... declare @totalrows int select @totalrows = COUNT(*) from sys.objects select type, COUNT(*), 100.0*COUNT(*)/@totalrows AS Percentage, REPLACE(space(convert(int, 100.0*count(*)/@totalrows)),' ','*') AS Barchart from sys.objects group by type gives the following: type Count Percentage Barchart ---- ----------- ----------------------------- ---------------------------------------------------------------------------------------------------- D 1 1.351351351351 * IT 4 5.405405405405 ***** P 2 2.702702702702 ** S 58 78.378378378378 ****************************************************************************** SQ 3 4.054054054054 **** U 6 8.108108108108 ********
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I added in my code above. I am still having issue decalring the totalrows to divide by
0 Likes 0 ·
I believe you're an Oracle guy - I can't help with that. Sorry.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.