Get Percent Column - Query
Jan 17, 2008
I have a data containing salesman who can belong to groups. The groups have "split" values but the splits are not on a percent basis. Can I create a query to get the percentage in a column? Please look at the table below and my attempted query.
Salesman GroupSplitSplitPercent
AG110.33
BG110.33
CG110.33
YG20.50.5
ZG20.50.5
UPDATE Splits
SET SplitPercent = Split / (SELECT SUM(Split) FROM Splits GROUP BY Splits.Group HAVING Splits.Group = ???)
View 2 Replies
ADVERTISEMENT
Dec 29, 2014
I am using SQL Server and have a table that is currently set to decimal(5,2) as the datatype. I am using Tableau as my front-end and if I create a dashboard, I can see my fields like 65.25, 65.72, etc. I want to view them as 65.25% however when I change the data type in Tableau to a %, it shows up as 6525.25%. Is there a way in the SQL Server side to change the column so it shows as a percent? I am sure the answer is no because I have never seen that. Only seen decimal values. Maybe I need to change it to decimal 2.2 if that even exists.
View 3 Replies
View Related
Jun 19, 2007
I have used SSRS 2005 to create a matrix and need to add a % of variance column. Is this possible? I hope I'm missing something simple. Basically the report should look like this:
Item CurVol PriorVol % of Var CurSales PriorSales % of Var
abc 100 90 11% 1250 990 26%
cde 96 128 -25% 192 243 -21%
Dataset looks this this:
Select item, vol, sales, 'Current' AS Per
FROM Table
WHERE (invoice_date >= @curStartDate) AND (invoice_date < @curEndDate + 1)
UNION
Select item, vol, sales, 'Prior' AS Per
FROM Table
WHERE (invoice_date >= @priorStartDate) AND (invoice_date < @priorEndDate + 1)
The column group on the matrix is using the Per column because the accounting periods cross over calendar months.
gusina
View 4 Replies
View Related
Apr 9, 2008
ID
TestID
IsCorrect
294817
1
1
294817
1
0
294817
1
1
294817
1
0
301391
2
1
301391
2
1
301391
2
1
301391
2
0
304409
1
1
304409
1
0
304409
1
0
304409
1
0
I am trying to get the percent correct for each ID and each testID from the table above. Also, IsCorrect column valuse (1= correct; 0= not correct)
select ID, count(Iscorrect) [total], (select count(Iscorrect) from dbo.StudentTestAnswers
where TestID=1 and Iscorrect=1) as totalCorrect,((select count(Iscorrect) from dbo.StudentTestAnswers
where TestID=1 and Iscorrect=1)/count(Iscorrect)*100) as PctCorrect
from StudentTestAnswers
where testID=1
Group by ID, TestID
The output should look like this after running the T-SQL code above. But I am getting some weird number on totalCorrect and PctCorrect columns. Not sure if my SQL code is even correct.
ID
total
totalCorrect
PctCorrect
294817
4
2
50%
301391
4
3
75%
304409
4
1
25%
View 7 Replies
View Related
May 8, 2008
I have a table of data, shown below that is composed of a scoreid, player id, and score. Using the following SQL Query:
"Select Top 50 Percent(score), playerid, scoreid from TScores
where playerid = 5
order by score"
I can get the lowest 50% of player number 5's scores.
How do I rewrite the query to give me each individual player's top 50 percent.
ScoreID
PlayerID
Score
10166
2
84
10167
2
80
14921
2
89
15069
2
95
13575
3
81
14282
3
84
12989
3
84
15821
3
89
16795
3
87
16950
3
85
17567
3
87
16948
5
79
16622
5
82
16590
5
85
11335
5
87
14279
5
82
13589
5
75
13973
5
77
15513
5
77
10070
5
81
10071
5
85
10049
6
86
10050
6
85
10051
6
98
16712
6
85
16710
6
85
View 8 Replies
View Related
Aug 21, 2007
The following query returns a value of 0 for the unit percent when I do a count/subquery count. Is there a way to get the percent count using a subquery? Another section of the query using the sum() works.
Here is a test code snippet:
--Test Count/Count subquery
declare @Date datetime
set @date = '8/15/2007'
select
-- count returns unit data
Count(substring(m.PTNumber,3,3)) as PTCnt,
-- count returns total for all units
(select Count(substring(m1.PTNumber,3,3))
from tblVGD1_Master m1
left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID
Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9
and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0
and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)
and v1.[Date] between DateAdd(dd,-90,@Date) and @Date) as TotalCnt,
-- attempting to calculate the percent by PTCnt/TotalCnt returns 0
(Count(substring(m.PTNumber,3,3)) /
(select Count(substring(m1.PTNumber,3,3))
from tblVGD1_Master m1
left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID
Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9
and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0
and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)
and v1.[Date] between DateAdd(dd,-90,@Date) and @Date)) as AUPct
-- main select
from tblVGD1_Master m
left join tblVGD1_ClassIII v on m.SlotNum_ID = v.SlotNum_ID
Where left(m.PTNumber,2) = 'PT' and m.Denom_ID <> 9
and v.Act = 1 and m.Active = 1 and v.MnyPlyd <> 0
and not (v.MnyPlyd = v.MnyWon and v.ActWin = 0)
and v.[Date] between DateAdd(dd,-90,@Date) and @Date
group by substring(m.PTNumber, 3,3)
order by AUPct Desc
Thanks. Dan
View 1 Replies
View Related
Oct 21, 2004
I have the following query where I am selecting the random row. However I need to select 10% randon rows from the tables. How do I do this?
select top 1 *
from table
ORDER BY NEWID()
View 1 Replies
View Related
Apr 2, 2004
I need rate to be a percent, it is returning 1 where XX=YY (e.g 100/100=1), but when the number (rate) is a fraction (90/100=.9)I am getting 0. Don't know what the problem is.
select XX as X, YY as Y, (YY/XX) as rate from blah where blah group by XX, YY order by XX
View 1 Replies
View Related
Nov 17, 2007
Hi all
whether using TOP clause in SELECT statement or [SET ROWCOUNT n] before SELECT statement, I want to know how SqlServer Behave?
whether Fetching data and then choosing n record of them or as soon as fetching n records , Sql Server Stops retrieving the rest of the data?
Thanks in advance.
Regards.
View 1 Replies
View Related
Jan 14, 2005
I have questions table and questions are categorized. I want to query say Top 10 questions but with some percentage based on category. For example, if I have 10 questions in category 1 and 50 questions in category 2 than when I select Top 10 with some percentage I want to come out more questions from category 2 than category 1.
View 6 Replies
View Related
Dec 17, 1999
I'd like to understant the Percent Log Used behavior...
I monitored the Percent Log Used. For a specific database, it was 50 % used. I backup up the transaction log and the Percent Log Used was still 50 % used. For another database, the log was 60 % used. After the backup, it was 80 % used!!!!
Would you help me to understant this situation?
Thank you,
Fabio
View 2 Replies
View Related
May 2, 2008
Hi,
How can I convert my number results to Percent (%) with 2 decimal places?
Thanks
View 12 Replies
View Related
Jun 12, 2008
I am trying to take a string code seen below that gives me the income over a 12 month time frame but now I need to take this string and divide it by the acqcost. and it will not let me can you please take a look at this and see whats wrong.
(This string is what gives me the income)
=sum(Fields!Total.Value)-Sum(IIF(Fields!eqprecdt.Value< #6/1/2007#,Fields!bookvalue.Value* 0.07,Fields!bookvalue.Value* 0.07*-1 * datediff("d",#5/31/2008#,Fields!eqprecdt.Value)/365))
(This string is the acq cost)
SUM(Fields!acqcost.Value)
View 6 Replies
View Related
Feb 23, 2006
Hi..
I have basic level question..
Why would we use SELECT TOP 100 PERCENT in a SQL Server 2000 query?
T.I.A
Papillon
View 12 Replies
View Related
Nov 13, 2007
Hi all
As you are aware we have TOP n [Percent] keyword that is used in Select statements ,I have a question??
When this keyword is evaluated by Sql-Server, after retreiving all Query-Result or upon the number of results reach to the specified number,sql-server stops
retrieving the rest of record.?
Thanks in advance.
regards.
View 3 Replies
View Related
Mar 20, 2008
hai everybody,
i have doubt With the Top(n) percent keyword
for example consider the table employee with following details i'm using a query
SELECT TOP(19) PERCENT * FROM Employee
if i execute this query im gettin 2 record set whose id is less than
6
SELECT TOP(19) PERCENT * FROM Employee WHERE ID <6
like wise if i execute the above query i'm gettin only one record set instead of two record set ...
why so, can anyone help me on this??...
id name salary st_date city region
1Jason404201994-02-01 00:00:00.000New YorkW
2Robert144201995-01-02 00:00:00.000VancouverN
3Celia240201996-12-03 00:00:00.000TorontoW
4Linda406201997-11-04 00:00:00.000New YorkN
5David800261998-10-05 00:00:00.000VancouverW
6James700601999-09-06 00:00:00.000TorontoN
7Alison906202000-08-07 00:00:00.000New YorkW
8Chris260202001-07-08 00:00:00.000VancouverN
9Mary600202002-06-09 00:00:00.000TorontoW
Thanks in Advance
View 3 Replies
View Related
Mar 24, 2008
I a bit of newbie making reports and I cant figure out how to calculate the Percent of Total on a report. Assuming I have a field in my datatable called Color, I simply want to create a report that counts the occurences of each color and then perform a calculation as to what percent of the total that each of the colors make up. Basically a report that looks like this....
Color Count Percent
Red 50 25%
Blue 100 50%
Green 50 25%
Total 200
I have a table that has a header, a grouping row based on Color, and a footer. So using the report expressions, my table looks like this
Color Count Percent
=Fields!Color.Value =Count(Fields!Color.Value) WHAT EXPRESSION GOES HERE?!?!?!
Thanks!!!
Mike
View 5 Replies
View Related
Jun 14, 2004
I have a script file which do the replication and in the script there is a lot of table need to be addad as article.
So while use osql execute the script there would be cost a lot of time. What I want to do is show a dialog which could show the progress percent and the file name which is now being addad as article.
hwo can i use Transact-SQL to do this?
Thanks a lot
View 4 Replies
View Related
Dec 24, 2013
I am trying to use this command to change the variable in where clause each time:
psexec servername -E cmd.EXE /c "sqlcmd -S servername /E -d dbname -v entertext="'%anything%'" -i c:myfoldermyscript.sql -o c:mypathoutput.sql"
myscript.sql is like:
select * from table_name
where summary like $(entertext);
This command works just for variables with single and exact word. It does not work for variables which has space between them or when I enter part of summary.In fact, I wanted to write something which the user be able to enter variable each time when he/she run this batch file. because I put this sqlcmd command in batch file( like this in oracle: "select * from table_name where summary = &entertext; " I was looking the same command in sql server , but I did not find it). I do not know .net or any other programing language( I know just csh and command line). I want to do this just with sql command.
View 3 Replies
View Related
Sep 26, 2013
I have a question about rounding and converting to percentage and adding in the '%'..This is my original code
SELECT * FROM
( SELECT Baby,
CAST( CAST( SUM(TotalBaby) AS DECIMAL )/ CAST( SUM(TotalParent) AS DECIMAL) AS DECIMAL(10,4)) as BabyValue
FROM NewBorn
WHERE Category = 'Boy'
[code]....
But I also need my data to have the '%' and it should have 2 decimal place which is as below, I have tried to make it this way
CAST( CAST( SUM(TotalBaby) AS DECIMAL )/ CAST( SUM(TotalParent) AS DECIMAL) AS DECIMAL(10,4)) * 100 + '%' as BabyValue
But it prompt me the error 'Error converting data type varchar to numeric.'
A B C D
0.22%0.29%0.11%0.32%
View 2 Replies
View Related
Jul 20, 2005
Hi!How can I get percent of executed procedure in MSSQL Server, lik inEnterprise Manager and/or dbMgr2k ?I use Visual C# and MSDE.Thank's for help, gregory
View 1 Replies
View Related
Nov 15, 2007
Hello,
This is my issue. I have values in my table that I need to format as a percent. So when I use the FormatPercent() function, it works but if one of the records has no value for that field. It generates a #Error message in that textbox. I tried using an if statement and that didn't work either. Someone please help me with this. Any information is appreciated.
View 17 Replies
View Related
Oct 30, 2007
I am trying to get a percent of a group on a row level. I want 62.77% or = sum(totaldeals, "Status") / sum(totaldeals, "Product"). It seems real easy but I have not been able to come up with a solution for it.
Any one have any ideas?
Product is a group, status is a group, total deals is a sum. The reason for a matrix is because I have fiscal year, fiscal quarter, fiscal month, and additional product lines coming on board. Matrix is the way to go for sum but I can't seem to get the % by product.
I was able to get the % on the row level but based on the report total with the following code:
=IIF(Sum(Fields!GrossSales.Value,"matrix1_Product")=0,0,Sum(Fields!GrossSales.Value)/IIF(Sum(Fields!GrossSales.Value,"matrix1_FianceStatus")=0,1,Sum(Fields!GrossSales.Value,"matrix1_FinanceStatus")))
This is the concept of what I am trying to do but I want it based on the on the product level sum not the matrix level sum. I tried changing the scopes of the above expression with no luck. I have tried adding inscope("") with no luck.
Product
Status
Total Deals
Total Dollar Amount
% of Total Dollars
Windows
A
1,748
$10,760,596
62.77%
C
286
$1,821,487
10.63%
F
331
$1,980,658
11.55%
H
P
-
$0
0.00%
Y
399
$2,579,597
15.05%
Total
2,764
$17,142,338
100.00%
View 3 Replies
View Related
Apr 18, 2005
I am having a problem retaining the right 2 decimal places in my datagrid field. I have it defined as a decimal but when the update happens it rounds the decimal places back to .00.
Dim NewMarkup As String
'retrieve the new values
NewMarkup = CType(e.Item.FindControl("Markup"), TextBox).Text
'create the ado.net objects
Dim command As New SqlCommand("UpdatePricing", connection)
command.CommandType = CommandType.StoredProcedure
'parameters
command.Parameters.Add("@Markup", SqlDbType.Decimal, 5)
command.Parameters("@Markup").Value = CDec(NewMarkup)
Thanks!
View 2 Replies
View Related
Nov 25, 2005
I have a problem with the following query in SQLExpress:
SELECT TOP (100) PERCENT ClientSurname, ClientName
FROM dbo.Client
ORDER BY ClientSurname, ClientName
The query returns always assorted data ignoring the ORDER BY keyword, no matter if the query is invoked directly from Management Studio Express CTP as a View, Table-valued function or called from an Access ADP project. The result is always assorted.¨
Now an interesting thing is that the syntax below returns always an expected order:
SELECT TOP (99) PERCENT …
SELECT TOP 10000 …
Am I missing something or is it a bug in SQLExpress?
View 2 Replies
View Related
Apr 13, 2006
I have a following table :
ID ------- Answear ------ Vote
1 ------- 1|2|3|4 ------- 3|5|3|2
2 ------- 1|2|3|4 ------- 2|5|3|1
3 ------- 1|2|3|4 ------- 2|5|7|2
So, I need to create : ,
ID ----- Answear ------ Vote ----- Total ----- Percent
1 ----- 1|2|3|4 ------ 3|5|3|2------ 13-----23.08|38.46|23.08|15.38
2 ----- 1|2|3|4 ------ 2|7|8|1------ 18-----.....
3 ----- 1|2|3|4 ------ 2|5|7|2------ 16-----.....
Any one help me ?
Thanh you very much.
PS :(3/13)*100|(5/13)*100|(3/13)*100|(2/13)*100 after calculate : 23.08 % |38.46 %|23.08 %|15.38 %
View 6 Replies
View Related
May 14, 2008
Hiya,
I have a need for a complex SQL statement to provide reporting information, but the SQL is way over my head and although I have some of the elements, I can't seem to pull them together to create a working SQL statement.
My database structure is as outlined below:
tblDef
------
ID IRVitems
ABC A1,A2,A3,A4,A5
tblIRV
------
ID IRVvalues
001 1,5,text1,10,3
002 2,1,text2,1,3
003 2,4,text3,1,1
004 1,4,text4,4,1
005 1,2,text5,4,1
tblLabel
--------
lblID lblVALUE lblTXT
A1 1 AAA
A1 2 BBB
I currently query tblDef to get IRVitems and in turn then query the other two tables to get statistical information to present in a report (via ASP).
For each item returned from IRVitems, I would like to report the spread and frequency of different values as follows:
A1
Value Label Count Percent
1 AAA 3 60
2 BBB 2 40
and then for A2, etc (although missing out A3 as it is a text item, but I can deal with that via ASP code beforehand). I would expect to have to hit the DB for each of the IRVitems (so once for getting the data for A1, againfor A2, etc).
I was provided a great bit of code to use SUBSTRING and GROUP BY to get this data (thanks zuomin), but I didn't consider the possible text values or numbers >9 when defining what I was trying to do (to be fair, they're new requirements):
SELECT SUBSTRING(IRVvalues, 1, 1) AS Value, COUNT(ID) AS Count
FROM tblIRV
GROUP BY SUBSTRING(IRVvalues, 1, 1)
ORDER BY value
which returns like this:
Value Count
1 187
2 163
3 2
Can I do a similar query to get all the info I need in one go? I saw the articale on a user-defined 'split' function (http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=StringArrayInput&referringTitle=Home) that can be used to split up the IRVvalues string to access the position I need in the array, but I'm a little clueless on how to then embed that into a SQl statement.
Can anyone point me in the right direction please?
View 3 Replies
View Related
Apr 14, 2006
Hi,
We are running sql server 2005 standard edition. In the query below, it has top percent clause in the subquery and returns various rows, 2, or 3, or 4 rows each times when running manually one after another. Does anyone have an explaination?
SELECT
dbo.WOB_STEP.STEP_UID
FROM
dbo.WOB_STEP
INNER JOIN dbo.CURRENT_DATES CD1 ON (dbo.WOB_STEP.CD_UID=CD1.CD_UID)
WHERE
( dbo.WOB_STEP.STEP_UID IN (SELECT TOP (50) PERCENT WITH TIES STEP_UID FROM dbo.vWOB_STEP_RANDOM WHERE OPERATOR_NAME = 'Jennelyn Llobrera' AND VENDOR_RETURN_FLAG = 1
ORDER BY RandomID) )
Thanks very much in advance !!!
View 3 Replies
View Related
Oct 11, 2015
I want to create a subset of a geography table. My intention is to select only a few cities with a where and after that select a percent of village for each of the cities. My query look like this:
 SELECT TOP 160 [CodMunicipio] ,[NombreMunicipio] ,[CodProv],[Provincia] ,[COMUNIDAD AUTÓNOMA]
   ,[Longitud ETRS89/REGCAN95],[Latitud ETRS89/REGCAN95]
   ,[XUTM ETRS89/REGCAN95],[YUTM ETRS89/REGCAN95],[Bandera]
  --INTO [PracticasSCD].DBO.DIMGEOGRAFIA
FROM [SportFunDW].[dbo].[DimGeografia2] TABLESAMPLE (3 PERCENT)
[Code] ....
I have tried with tablesample but it doesn´t return what I expect because I have many villages from one city (for example 15 % of it) and only 2 % of other. How can I achieve my goal?
View 5 Replies
View Related
Mar 13, 2008
Script below returns 29 rows, must return 28!
Code Snippet
use master
select top 25 percent
id
from
(
select top 112
*
from
syscolumns
) d
order by
id desc
Script below returns 57 rows, must return 56!
Code Snippet
use master
select top 50 percent
id
from
(
select top 112
*
from
syscolumns
) d
order by
id desc
Reproducable on
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
and
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Could anyone from MS SQL server team clarify what's happened and WHAT TO DO?
View 4 Replies
View Related
May 8, 2008
Does anyone know how to get over their dreadfull divide by zero error. I keep getting the error, i have changed this code so much. I need to get the percent increase from 2007-2008 ("Total") in this table grouping. I know im getting the error in some of the fields, because sometimes the total is 0 and sometimes the 2007 is 0. What do i do?
Code Snippet
=IIF(Sum(iif(Fields!Column_Text.Value = "Total", Cdbl(Fields!Period_1.Value), 0.0))=0,"0%",
Sum(iif(Fields!Column_Text.Value = "Total", Fields!Period_1.Value,nothing))- Sum(iif(Fields!Column_Text.Value = "2007", Fields!Period_1.Value, nothing)))/ IIF(Sum(iif(Fields!Column_Text.Value = "2007", Fields!Period_1.Value, nothing))=0,1,Sum(iif(Fields!Column_Text.Value = "2007", Fields!Period_1.Value, nothing)))
View 1 Replies
View Related
Feb 7, 2006
Hi AllI have just upgraded to SQL2005 and found an annoying problem. WheneverI add a new view or amend an existing one (created in SQL 2000Enterprise Manager) it inserts TOP 100 Percent into the SQL query. Evenif I delete the clause from the SQL query it puts it straight back inagain. This is a problem as there are some queries where I just don'twant this clause there (ADO.NET cannot update a view with a TOP clause)Any ideas anyone?Many thanksIan Bell*** Sent via Developersdex http://www.developersdex.com ***
View 3 Replies
View Related
