• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 141
  • Last Modified:

SQL Percentage - Bring back full percentage

Hello,

Looking for a little help and a better understanding about why I am not able to bring back the full percentage.  I am looking to get the percentage of each model item ordered.  I am having difficulty with the rounding, I am only bring back full numbers.  

select orders_items_model, count(orders_items_model) as TotalNumber 
from orders_items
group by orders_items_model
order by TotalNumber desc

Open in new window



gives me
+---------------------------------+------------------------------|
|Orders_items_model   |  TotalNumber            |
-------------------------------------------------------------------
| MDUES                          | 63641                          |
| RBK100                         | 13833                           |
| RBK101                         | 6851                             |
| CRT500                          | 1686                            |
| PRA100                          | 1481                            |
| CAR100                          | 1260                            |
| RPT101                          |  23                                |
+--------------------------------+-------------------------------+

I am trying to get the actual percentage.  The query I have only gets full numbers and leaves anything below 1 as 0.  Where am I going wrong at?


select orders_items_model, round(cast(count(*) * 100 / sum(Count(*)) over () as decimal(18,2)),0) as Percent1
from orders_items
group by orders_items_model
order by Percent1 desc

Open in new window

 


+---------------------------------+------------------------------|
|Orders_items_model   | Percent1                    |
-------------------------------------------------------------------
| MDUES                          | 68.00                          |
| RBK100                         | 14.00                           |
| RBK101                         | 7.00                             |
| CRT500                          | 1.00                            |
| PRA100                          |1.00                             |
| CAR100                          | 1.00                            |
| RPT101                          |  0.00                            |
+--------------------------------+-------------------------------+


Thanks for any help.
0
lovettjay
Asked:
lovettjay
4 Solutions
 
Mike EghtebasDatabase and Application DeveloperCommented:
See my article: "Subqueries at a glance" at
https://www.experts-exchange.com/articles/18366/Subqueries-at-a-glance.html

Next I will try to apply one of these subquery methods to do what you are asking for.

Mike
1
 
Aaron TomoskyTechnology ConsultantCommented:
The order matters, sql puts the first thing into the second thing. So cast(1.2345 as decimal (18,2)) * 100 becomes an integer because 100 is auto detected as an integer. But 100* 1.2345 will have decimal points.
2
 
_agx_Commented:
(no points...)

>> The order matters

True. Since COUNT returns an integer, you need to cast one (or both) of the values to a decimal before dividing. Otherwise, you are doing INTEGER division, and any decimals will be truncated.

SELECT cast(1 as int) / cast(2 as int)   <=== result is 0
SELECT cast(1 as decimal(18,2)) / cast(2 as decimal(18,2)) <=== result is 0.50000000000000000000
1
 
PortletPaulCommented:
no points...  try using one hundred point zero

(count(*) * 100.0) / (Count(*) over ())
2
 
lovettjayAuthor Commented:
Thank you for your help and assistance.  Adding the .0 after 100 worked perfectly.  Also thanks for the article to read and "sql order" matters post with the examples
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now