Solved

SQL Percentage - Bring back full percentage

Posted on 2016-11-06
5
93 Views
Last Modified: 2016-11-07
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
Comment
Question by:lovettjay
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 34

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 100 total points
ID: 41876312
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
 
LVL 39

Assisted Solution

by:Aaron Tomosky
Aaron Tomosky earned 100 total points
ID: 41876329
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
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 150 total points
ID: 41876334
(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
 
LVL 48

Accepted Solution

by:
PortletPaul earned 150 total points
ID: 41876391
no points...  try using one hundred point zero

(count(*) * 100.0) / (Count(*) over ())
2
 

Author Closing Comment

by:lovettjay
ID: 41877271
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question