# Calculating Median Values On Column In A Table

Feb 19, 2012

I need to calculate a median on a column in a table. The code I have is:

Code:
Select gender,
CASE
when gender = 'F' then 'Female'
when gender = 'M' then 'Male'
else 'Unknown'
end as test,
from [tbl_record]
How do I calculate the median on the datediffcal column?

It doesn't matter if the resultset only shows the median result. So if the output shows:

median
15

that's fine. Minimally, I need the median value.

## Calculating Median In SQL

Aug 25, 2004

Hi All,
I have a table that of server names and their execution times that run in to hundreds of thousands of records. What i need is some SQL that gives me the median execution times for each of these different servers. At the moment i have some SQL that only gives me the median for all the records in the table not the median execution time for every different server name. For example my tables looks something like this;

ServerName | ExecTime
-----------------------
server1 | 0.07
server2 | 0.17
server1 | 0.27
server1 | 0.37
server2 | 0.47
server1 | 0.57
server1 | 0.67
server2 | 0.77

My SQL below gives me

ServerName | ExecTime
-----------------------
server1 | 0.37

Where as i want

ServerName | ExecTime
-----------------------
server1 | 0.37
server2 | 0.47

Here is my SQL, hope someone can modify it and thanks in advance.

Code:

SELECT DISTINCT instance, exec_time AS median
FROM (SELECT instance, exec_time
FROM (SELECT TOP 1 exec_time = exec_time * 1.0, instance
FROM (SELECT TOP 50 PERCENT exec_time, instance
FROM llserverlogs
ORDER BY exec_time) sub_a
ORDER BY 1 DESC) sub_1
UNION ALL
SELECT instance, exec_time
FROM (SELECT TOP 1 exec_time = exec_time * 1.0, instance
FROM (SELECT TOP 50 PERCENT exec_time, instance
FROM llserverlogs
ORDER BY exec_time DESC) sub_b
ORDER BY 1) sub_2)

## Calculating Median Value From Measures And Dimensions

May 24, 2007

I am facing some problem in calculating Median

I am trying to calculate the median value using one of the measures and a dimension value.

Time is a measure in my cube and OpId is one of the dimensions.The result is as follows:

opid time median

1 55

2 23

3 23

Total 23

The Time here for Op Id 1 is the aggregation for all the rows whose OpId is 1.I want the median of the values whose OpId is 1 which is not showing at the moment.

What I am getting here is the median for all of the OpId but what I really want is the median for each of the individual Opid's as well.

I am using a calculated field Median with the following expression.

MEDIAN

( [Dim Operation].[Dim Operation].currentmember.children ,[Measures].[Elapsed Time])

Thanks

## SQL Server 2008 :: Calculating Median Over 2 Columns?

Aug 13, 2015

I have a database with 1million+ records in and i'm trying to collect the median values of column(2) for all distinct values in column (1)

Example DB:

Column 1 Column 2
978555 500
978555 502
978555 480
978555 490
978324 1111
978324 1102
978311 122
978311 120
978994 804
978320 359

and I need it to display on SELECT as

column 1 column 2
978555 495
978324 1106
978311 121
978994 804
978320 359

Is this possible on 2008 R2?

## Analysis :: Calculating A Rolling Median Over A Period Of 3 Years?

Jun 17, 2015

calculating a rolling median over a period of 3 years.

I already calculate median and I've tried to calculate rolling median over a period of 3 years as below.

MEDIAN([Date].[Year].CurrentMember.Lag(3):[Date].[Year].CurrentMember,[Measures].[median])

What this does is, it calculates the median of the medians over the period of 3 years. But, what I'm looking for is the overall median of the underlying measure over a period of 3 years.

What I have now:

Year1 - 41,52,73;  Median1 - 52
Year2 - 6,9,12;  Median2- 9
Year3 - 24,68,89; Median3 - 68
Overall Median of 9,52,68 - 52

What I need:

Year1 - 41,52,73;  Median1 - 52
Year2 - 6,9,12;  Median2- 9
Year3 - 24,68,89; Median3 - 68

Overall Median of 41,52,73,6,9,12,24,68,89 is 41

## Calculating Column Values

Sep 19, 2007

Hi, this is my problem. The query listed below gets a list of employees asigned to a project, to a customer and to a business unit (the Dimension field). The last two fields are the start date and the end date.

select
P.DIMENSION, P.CUSTACCOUNT, C.NAME as CUSTOMERNAME, PE.PROJID, P.NAME as PROJNAME, PE.EMPLID, E.NAME as EMPLNAME,
from
PROJVALEMPLPROJSETUP PE, EMPLTABLE E, PROJTABLE P, CUSTTABLE C
where
PE.PROJID = P.PROJID and PE.EMPLID = E.EMPLID and P.CUSTACCOUNT = C.ACCOUNTNUM and (PE.FECHAASIGNACION >= '01/01/2007'
order by
P.DIMENSION, P.CUSTACCOUNT, PE.PROJID, PE.EMPLID

I need to get a listing showing how many employees started or ended their projects every months in the following format:

Business UNIT CLIENT PROJECT STARTING TOTAL JANUARY FEBRUARY .. TOTAL VARIATION
--------------------------------------------------------------------------------------------------------
1 1 1 25 3 -2 26 1

Starting total refers to all the employees that have a starting date before 1/1/2007 and a ending date after 1/1/2007

The monthly columns would be the total of employees with a starting date (+1) or ending date (-1) in that month.

The total column is the starting total plus the monthly totals.

And the variation is the difference of the total with the starting total.

Any ideas?

Thanks a lot.

## Power Pivot :: Calculating Values For Future Dates Based On Past Values

Nov 13, 2015

I am working with a data set containing several years' of monetary values. I have entries for past dates and the associated values, and I also have entries for future dates. I need to populate the values of the future date records with the values from the same date the previous year. Is there any way this can be done in Power Pivot?

## Table Column Names = Dataset Column Values?!

Apr 28, 2008

I need to create the following table in reporting services

PRODUCT April March Feb

2008 2007 2008 2007 2008 2007
chair 8 9 7 4 4 4
table 3 4 5 6 4 6

My problem is the month names are a column in the dataset, but I dont know how to get it to fill as column headers???

## Simulating Median On Table Columns In SQLserver2000

Apr 22, 2004

Folks,

I have a an sql table having 10,000 Rows.

The table has 5 columns. They are S1,S2,S3,S4 and M

I need the best way to calculate the MEDIAN for each row and store it in M. i.e. M=median(S1,S2,S3,S4)

I've come across articles where they've calculated medians on table rows, not table columns. But my requirement involves table columns. I guess transposing the columns into rows and then calculating median should be possible, but if I do that for 10000 rows using a cursor, then it would take a loooooong time.

Please suggest the best way to counter this

Thanx

Kiran

## Calculating With Null Values

Mar 7, 2005

I have 2 separate queries, one produces a total and one doesn't at the moment as the criteria are not met.

I want to add the total field from both these queries together but becuase one is null value, the sum total is also null.

In access I would have used nz([Qry_1].[Total])+nz([Qry_2].[Total]) to get over this but SQL/adp doesnt seem to understand this.

Can you help?

## Calculating With Null Values

Mar 7, 2005

I have 2 separate queries, one produces a total and one doesn't at the moment as the criteria are not met.

I want to add the total field from both these queries together but becuase one is null value, the sum total is also null.

In access I would have used nz([Qry_1].[Total])+nz([Qry_2].[Total]) to get over this but SQL/adp doesnt seem to understand this.

Can you help?

## Column Name Or Number Of Supplied Values Does Not Match Table Definition When Trying To Populate Temp Table

Jun 6, 2005

Hello,

I am receiving the following error:

Column name or number of supplied values does not match table definition

I am trying to insert values into a temp table, using values from the table I copied the structure from, like this:

SELECT TOP 1 * INTO #tbl_User_Temp FROM tbl_User
TRUNCATE TABLE #tbl_User_Temp

INSERT INTO #tbl_User_Temp EXECUTE UserPersist_GetUserByCriteria @Gender = 'Male', @Culture = 'en-GB'

The SP UserPersist_GetByCriteria does a
"SELECT * FROM tbl_User WHERE gender = @Gender AND culture = @Culture",
so why am I receiving this error when both tables have the same
structure?

The error is being reported as coming from UserPersist_GetByCriteria on the "SELECT * FROM tbl_User" line.

Thanks,
Greg.

## Making A View For Calculating Values

Apr 17, 2008

Hello, I have 2 tables, first is the mineral values, 2nd is ore composition... Minerals table:

Mineral
Value

Mineral1
10

Mineral2
20Ore composition table:

Ore
Mineral1
Mineral2

Ore1
100
0

Ore2
0
200

Ore3
10
10Now what I need to do is to have a view (or something else that gerenates a table) that will give me the value of each ore. So for example ore 3 would be 10 X mineral1 value plus 10 X mineral2 value. So the result should be:

Ore
Value

Ore1
1000

Ore2
4000

Ore3
300Can anyone point me to some tutorial or example of a similar calculation with sql views?

## Need Help Calculating Values In View Code

Jan 26, 2008

I'm in the process of building various queries and I'm having issue calculation values in the following code. I need to calculate the maximum and minimum salary for exempt employees and include a column that calculates the difference in these numbers. I have no issue creating views and outputing data. When I try to calculate the values I receive errors:

My code is:

GO
IF OBJECT_ID ('Salaries') IS NOT NULL
DROP VIEW Salaries ;
GO
CREATE VIEW Salaries
AS
select max(Salary) from EMPLOYEE join JOB_TITLE on EMPLOYEE.JobID = JOB_TITLE.JobID where ExemptStatus='Exempt'
Group By Salary, ExemptStatus;
GO
SELECT * FROM [Salaries]

I'm receiving the following
Msg 4511, Level 16, State 1, Procedure Salaries, Line 3
Create View or Function failed because no column name was specified for column 1.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'Salaries'.

Thanks folks..

## Calculating Change Of Values Between Rows

Aug 23, 2007

Hello forum.

I have three columns in my table with the following datatypes:

Date - DateTime
Height - Decimal
Change_From_last_Height - Decimal

I am using "SQL Server 2005 Express Edition". I'm fairly new to SQL and would greatly appreciate any help or advice I can get.

The "date" column increases by an extra day in every new row and I then enter the new height of the plant. What I want to know is how I can get SQL server express to automatically enter the difference in height between the current row's height and that of the previous row.

Is it possible to automate the entry in the Change_From_Last_Height column in SQL?

Put another way, I know how to find the difference between two values in the same row but different columns, but how do I calculate the difference between values in adjacent Rows (ie. Rows next to each other)?

Thank you.

## Reporting Services :: Count Values In A Column Based Upon Distinct Values In Another Column In SharePoint List

Sep 7, 2015

We have SharePoint list which has, say, two columns. Column A and Column B.

Column A can have three values - red, blue & green.

Column B can have four values - pen, marker, pencil & highlighter.

A typical view of list can be:

Column A - Column B
red  - pen
red - pencil
red - highlighter
blue - marker
blue - pencil
green - pen
green - highlighter
red  - pen
blue - pencil
blue - highlighter
blue - pencil

We are looking to create a report from SharePoint List using SSRS which has following view:

red     blue   green
pen            2       0      1
marker       0       1      0
pencil          1       3      0
highlighter  1       1      1

We tried Sum but not able to display in single row.

## Calculating Values In Multiple Tables Within A UDF In SQL Server

Feb 24, 2005

Hi,
I'm using scalar UDFs in SQL server to return computed values. I've been trying to use the same udfs to perform the same computations but from different tables, but I'm not sure how. Can someone please help???

Here's an example of a counter that I'm using to return the number of days.

CREATE FUNCTION [dbo].[MarketPulse_fn_Counter] (@date smalldatetime, @date2 smalldatetime, @osid int)

RETURNS int AS

BEGIN

return (select count(*) from MarketPulse_0ndqc
where stockosid = @osid and createdate <= @date and createdate >=@date2 )

END

How can I use this same UDF to do the same computation but to SELECT from another table and return that value?

## Integration Services :: SSIS Package - Replacing Null Values In One Column With Values From Another Column

Sep 3, 2015

I have an SSIS package that imports data from an Excel file, replaces any value in Excel that reads "NULL" to "", then writes the data to a couple of databases.

What I have discovered today, is I have two columns of dates, an admit date and discharge date column, and what I need to do is anywhere I have a null value in the discharge date column, I have to replace it with the value in the admit date column.

I have searched around online and tried a few things using the Replace funtion in Derived columns but no dice so far.

## Calculating Textboxes Values Grouped In Diferent Lists

May 7, 2007

I have to Lists filtered by the same field. One List shows all records with the field IsClokedIn value = TRUE and the other list =FALSE. I have only one dataset that retrieve the ClockIn and Clock out for one user. In a perfect world, the user MUST clock in and always clock out. So, when i created my dataset i ordered by UserClockingID (whick is primary key). So, this MUST retrieve a dataset with alternate records IN / OUT (TRUE or FALSE)

Ex.

UserClokingID UserID Time IsClockedIN

2323 34 8:32:03 TRUE

2324 34 12:07:02 FALSE

2325 34 13:05:03 TRUE

2326 34 14:53:02 FALSE

2327 34 15:10:03 TRUE

2328 34 17:32:02 FALSE

All i need is to calculate (sum then divide by 60) the minutes returned by the method DateDiff between the first row in my TRUE filtered list and the first row in my FALSE filetered list... and so on for the 2nd, 3rd ..... I can get the minutes by taking the values directly from the Fields!IsClockedIn.Value and using a condition to "grab" the previous record if the current record ClockedIn.Value=FALSE. I already did that.

my problem is that when i try to perform a calculation of textboxes values(those with the ammount of minutes for every pair of records from the datalist) out of the group. It says you can only do that inside the group or dataregion.

Is there any work around?

I really need to get this solved.

## Sum Of Column Values Of Each Row In Another Column Of Same Table

Feb 12, 2006

Hi,
I have got a table where i want to display sum of count(Column1), count(Column2) in another column.How can this be done?
for example
SELECT SUM(Count(pxInsName)+Count(pxFlowName)) AS "pySummaryCount(1)" , Count(pxInsName) AS "pySummaryCount(2)" , Count(pxFlowName) AS "pySummaryCount(3)" , pxAssignedOrg AS "pxAssignedOrg" , pxAssignedOrgDiv AS "pxAssignedOrgDiv" , pxAssignedOrgUnit AS "pxAssignedOrgUnit" FROM pc_assign_worklist WHERE pxObjClass = ? GROUP BY pxAssignedOrg , pxAssignedOrgDiv , pxAssignedOrgUnit ORDER BY 'pySummaryCount(1)' DESC

But sum function can not be used on aggregate function.

Is there any other way.

## Add Symbol To Column Values And Convert Column Values To Western Number System

Feb 12, 2014

I want to add \$ symbol to column values and convert the column values to western number system

Column values
Dollar
4255
25454
467834

Expected Output:
\$ 4,255
\$ 25,454
\$ 467,834

My Query:
select ID, MAX(Date) Date, SUM(Cost) Dollars, MAX(Funded) Funding from Application

COST is the int datatype and needs to be changed.

## Mapping Some Of The Values In Table To Another Table Column

Apr 17, 2008

Hi Guys

I have a table that contains codes for commodities.Some of the codes in this table have changed and some of them have not.So now i want to design a solution that enable me to map the new codes in a different mapping table to the old ones in the other table.I also want to retain the old codes because most of the archived data used the old codes.

The New code is the new code to which the Code must be mapped to.Where there is no new code, the current code is being retained.How do i design my table and queries so that i can use the new codes as if i was using the old code.I want to select products with a certain code but using the new code and mapping to the old codes or vice versa.

The structure of the data is like this.

New Code
Code
Name

GO01CCN
Gas Oil

XAA
AA
AA

XAL
AL
Aluminium

XMA
ALM
ALM

XAL
ALT
Aluminium in tonnes

XAR
AR
AR

AUD
Australian Dollars

AUJPY
AUJPY

??
CAQ
CAQ

XCC
CC
CC

XCU
CCF
CCF

CER
Carbon Emmission Reduction

Coal
COAL

XCR
CR
Copper Repo

XCT
CT
CT

XCU
CU
CU

XCU
CUC
CUC

## How To Add All The Values In A Single Column In A Table?

Oct 6, 2005

numbers     2     4     2Above is an example of my table with a single column. My problem is how to add all the numbers in that column to make it 8? The rows are also dynamic. Your help is highly appreciated.

## Differencing Values Of A Table Column

Aug 12, 2006

Hi, I would appreciate your help on this-
am trying to generate a new column Field2 from an existing column Field1, using a SELECT statement

e.g. if
Field1

3
2
6
1

then the new column is the first-difference in values of the original column:

Field2

<null>
-1
4
-5

is there an easy way to use the select statement, e.g.

SELECT Field1 - Field1~-1 AS Field2 FROM....

Thanks a lot,
Ned

## Parameter As Table Column Values

Sep 21, 2007

can we define a stored procedure parameter which holds a table's column values?
And can we loop in it in a stored procedure?

If yes, could you give a little sql code sample?

Thanx

## Non Matching Column Values In The Same Table

Mar 22, 2008

I need to write a statement that returns the name, city, and state of each vendor that’s located in a unique city and state. In other words, I can not include vendors that have a city and state in common with another vendor.

Any help?
Thanks.

## How To Know If A Column In A Table Has Decimal Values

Apr 4, 2008

Hi Guys,

I want to find out if a ceratin column has values with decimal figures populated? for example say column A has values

10.00
20.00
30.00
50.01

in this case the query should let me know that there is one figure (50.01) with a actual decimal value. So the results of the query should be 1 (since one record has decimal figures <>.00)

Cananyone help

Sonny

## Inserting Values Into A Column By Selecting Value From Different Table

May 5, 2004

Hi, I have a question regarding how to insert one column values into a table by selecting from different table..Here is the syntax..
------------
insert into propertytable values (select lastvalue+incrementby from agilesequences where name='SEQPROPERTYTABLE', 13926, 0, 4, 1, 451, 1, 8, 1)

the first column in the propertytable will be... select lastvalue+incrementby from agilesequences where name='SEQPROPERTYTABLE'
How do I do that..Help PLZ..

## T-SQL (SS2K8) :: Comparing Column Values In Same Table

Jun 16, 2014

How to resolve the below task

create table #temp ( idx int identity(1,1), col1 int, col2 int )

Here i want a flag success or fail on basis of below conditions.

I need to take all the col1 values and then i need to compare to each other.

if any difference found, i need to check difference more than 30, then it should raise the flag as "Failure".

if all the col1 values are ok , then we need to check Col2 values same as above.

--case 1

insert into #temp(col1,col2)
select 16522,18522
union all
select 16522,18522
union all
select 16582,18522

--select * from #temp

--truncate table #temp

Because of difference in col1 values . the value of flag should be fail.

--case 2

insert into #temp(col1,col2)
select 16522,18522
union all
select 16522,18522
union all
select 16522,17522

Here also the col1 is ok but col2 values have difference so it should be Fail.

Otherwise it should be success.

## UPDATE A RECORD FROM COLUMN VALUES OF OTHER TABLE

Jun 10, 2008

HI ALL,
CAN ANYBODY HELP ME TO SOLVE THE ISSUE
I WANNA UPDATE A RECORD OF ONE TABLE WITH VALUES THAT ARE PRESENT IN A COLUMN OF ANOTHER TABLE
OR

CAN I DO THE SAME USING INSERT QUERY

## Insert Values In To A Table Which Is Having Identity Column

Feb 8, 2007

Arif writes "I want to insert values in to a table which is having identity column, how? can you help me out from this problem.

Jamal"

## Comma Separated Values In A Column Of A Table

Jul 13, 2007

Hi,
I want a column in a database table to store comma separated values.
So can I store it as a string type(varchar,nchar) using commas?
What are the other alternatives provided in Sql Server 2005

--Subba Rao

## Adding Dataset Column Values To My Table At The End

Mar 14, 2006

Hi,

I am mapping an entity from SQL 2005 to another entity in another system on SQL 2000. Since the destination system has its own ID generator, I want to keep the generated ID for each row of my table in a column of my table in SQL 2005. The new column is in the dataset now , but I don't know how to update my table to have that column values (The OleDbDestination just insert new items.)

Samy