Problem In Calculating Sum For A Column

Apr 13, 2008

Hi,
Im having a table

table1
col1 col2 col3
1 1 1
1 1 1
2 1 2
2 1 2
1 3 1
1 3 1

im calculating sum of col1,col2 and col3

select sum(col1) + sum(col2) + sum(col3) from table1
where col1 =1 and
col2 = 1 and
col3 = 1
I have one problem
i want to get the sum of columns where the value is one
the output i want will be like this
12
but im getting output as
6
it is eliminating the rows where col1,col2 and col3 is not equal to 1

can anyone help

View 3 Replies


ADVERTISEMENT

Calculating Column W/ Another Derived Column

Jun 6, 2007

Can someone help me with the following query? I need to take the derived value from one column and calculate another column with it.

SELECT UserID,

((SELECT COUNT(*) FROM HPN_LeadMatches)+(SELECT COUNT(*) FROM HPN_Painters)) As Total,

(SELECT COUNT(*) FROM HPN_Leads) / Main.Total

FROM HPN_LeadMatches As Main

The error i'm getting says is unkown column 'total'. Is there another way to accomplish this?

Thanks!!

View 7 Replies View Related

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,
PE.FECHAASIGNACION, PE.FECHADESASIGNACION
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'
and PE.FECHADESASIGNACION >= '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.

View 1 Replies View Related

Calculating The Sum Of A Calculated Column

Mar 28, 2008

Hi,

I'm writing a query that calculates values for each id, then I need to sum up all the values for each id and put them in another field, here is what I wrote but SQL cannot understand the column that I calculeted

select id,term_cd,
case when RIGHT(term_cd, 1) IN ('6') and substring(term_cd, 2,4) <= 2004) then '1'
when substring(term_cd, 2,4) <= 2004 and RIGHT(term_cd, 1) = 5 then '0.5'
else '1' end as term_count,
SUM(term_count) AS ttd_enrolled
group by id,term_cd

SQL gives me an error "invalid column name tern_count" in the line where I calculate the sum.
what's wrong with the query? or should I calculate the term_count in an inner query?

Appretiate your help

View 8 Replies View Related

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,
datediff(day, [admit_date], getdate()) as 'datediffcal',
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.

View 5 Replies View Related

Reporting Services :: Calculating Percentage Of Column Total In Charts

Jul 22, 2015

Calculating % of Column Total in Charts...

Doing this exercise in Excel is always very simple: 

View 3 Replies View Related

Reporting Services :: Calculating Difference Between Columns Within Column Group

Jun 15, 2015

Given the attached report, is there an easy way of calculating the difference between the Today and QTR Start column? Because of the Account Group, the report looks like the sample shown on the second image.

Sample report:

View 4 Replies View Related

Calculating Percenatges On Matrix Report Form A Single Column

May 15, 2008

Hi,

I am sure there is a simple answer to this, but I cannot find it at the moment???

I have a simple data table in SQL which gives me Division, PL Measure and Value...









SQL Table






Division
PL_Desc
Result

A
Total Labour Costs
10

A
Total Sales (inc Machine Income)
100

B
Total Labour Costs
5

B
Total Sales (inc Machine Income)
100

C
Total Labour Costs
9

C
Total Sales (inc Machine Income)
100

I need to report on this and calculate a ratio, so I have pushed this into a Matrix Report but cannot work out how to get the ratio column to work???...









Matrix Report


???????????






Division
Total Labour Costs
Total Sales (inc Machine Income)
New Column = Labour / Sales

A
10
100
10%

B
5
100
5%

C
9
100
9%

Grand Total


24
300
8%


If anyone can help save me from my own stupidity!

Cheers, Matt

View 6 Replies View Related

T-SQL And Calculating A Sum

Sep 26, 2006

I want to sum up a column, but only summing up the top 25 quantites and then I want to subtract the sum of all quantities from the total of the top 25...how would this be done with t-sql?

View 3 Replies View Related

Calculating PK

Jul 2, 2001

SQL7

I am interested in your opinion on the creation of primary keys in a table - simple ones that is. It would be the replacement for an Identity column.

Some say calculate the value off a lookup table, others say use an external object like c++ dll reading/incrementing from a flat file.

Using an identity column reduces portability of the table.

Thoughts ?

Craig

View 2 Replies View Related

Help !!!calculating Age

Nov 15, 2001

Hi,
What the best way to calculate the age given two datetimes.
I tried using the DATEDIFF(yyy,startdate,enddate) but that seems to only compare the year and not look at the specific day.

I am looking for something that would return the following output


birthday= 12/6/1973
EndDate = 11/30/2001
then the age should be 27

If birthday = 12/6/1973
enddate = 12/11/1973
then the age should be 28

thanks in advance
Zoey

View 3 Replies View Related

Calculating RFM

Apr 17, 2007



I'm in the process of developing a Kimball-method CRM data warehouse and I'd like to have Recency, Frequency, Monetary "scores" at the customer level. Am I better off creating these in the relational database and feeding them to SSAS or have SSAS do the aggregations and assign the "scores"?



Also, has anyone located any SSIS transform scripts that would calculated RFM scores? This is probably a pretty easy task in VB.net but I haven't gone down that road yet.



Steve

View 1 Replies View Related

Calculating The Sum Of Columns...

Oct 31, 2006

It may be simple question, but how can i get the sum of column? with conditions like getting the some of clicks one user send and so...

View 5 Replies View Related

Calculating Ages

Jul 3, 2007

When users register with my site they give me their DoB, how can I work out an age from it. I was thinkin about doing it from the year, but that isnt very accurate. Can anyone help? thanks si! ps Id only want to work out how many years old they are. si! 

View 7 Replies View Related

Calculating ID Numbers

Mar 28, 2008

 I needed to come up with an algorithm to create unique user- friendly account numbers such as AC0000000001, AC0000000002, etc...Where they increment by 1. I created a SQL function that retrieves the previous number generated, adds 1 to it, inserts the new value into  the table, then returns the new value. I started thinking, what if the function is ran at the same time? What if function # 1 creates the new number, and function #2 creates a new number as well before function #1 inserts it? Is this a possible scenario? If so, how do I lock the process until the function completes to prevent this?  Thanks for any help you provide.

View 2 Replies View Related

Calculating The 3rd Value By The First 2's Selection

May 30, 2008

Hi,
First off, let me state that i am new to ASP.net... and have had to hit the ground running... I have looked on the net but have not found a suitable explanation on how to do this.
Let me explain what i am trying to do: In my aspx page, i have 2 dropdown boxes where the user will select a skill catagory, and then a skill. Based on that selection, i would like to compute what the Service line will be. There is a primary and foreign key on CatID on the skills and SkillCat tables. This data would need to be inserted into the another table aswell, and this would need to go into a report later.
How do i go about doing this? Some examples would be greatly appreciated. And if you know of a good link, please post it for me.
 Skills Table









CatID
SkillID
Skills
SL_ID

1
4
Skill 1
3

2
5
Skill 2
2

2
6
Skill 3
2

3
7
Skill 4
4

3
8
Skill 5
4

4
9
Skill 6
NULL

4
10
Skill 7
NULL

5
11
Skill 8
3

6
12
Skill 9
3

7
13
Skill 10
4

8
14
Skill 11
1

9
15
Skill 12
3

9
16
Skill 13
3

10
17
Skill 14
6

11
18
Skill 15
1
Skills Catagory








CATID
Org
Skillcat

1
Org1
Skill Category 1

2
Org2
Skill Category 2

3
Org3
Skill Category 3

4
Org4
Skill Category 4

5
Org5
Skill Category 5

6
Org6
Skill Category 6

7
Org7
Skill Category 7

8
Org8
Skill Category 8

9
Org9
Skill Category 9
 Service Line Table







SL_ID
Service_line

1
Service Line 1

2
Service Line 2

3
Service Line 3

4
Service Line 4

5
Service Line 5

6
Service Line 6

7
Service Line 7
 
Thanks for all your help in advance.
Dash

View 1 Replies View Related

Calculating Avg Rowsize In My Db...

Nov 23, 2001

Hi,

I'm using SQL Server 7.0 SP3.

I need to calculate the average size of a record in my database to assist in estimating growth.

Is there a quick and/or easy way of doing this?

Thanks in advance,
Darrin

View 1 Replies View Related

Help With Calculating Dates.

Aug 26, 2005

Hi,

I'm fairly new to MS SQL, but I'm trying to query the time elapse between two dates. Example below...

Select
dbo.res.book_date,
dbo.res.arrive_date,
Cast (dbo.translog.systemdate as datetime)
From
dbo.res
Where
dbo.res.resid > '500'

The third line is where I need help with a statement. I need the elapse time. Ex. if the book date is 20050820100000 and the arrive date is 20050820120000, I would like the third Select statement to return 2:00 hours or something similar. Thanks.

View 11 Replies View Related

Calculating Time

Aug 27, 2005

Hello,
I am fairly new to this, but I need to write a query that calculates whether an order made a shipping cutoff based on creation time, and when it actually shipped, based on local customer time. All times in the table below are central time.
Heres where it gets complicated for me. If the order was created after 8:00am local customer time the same day, and shipped prior to 5pm local customer time, it would be considered a pass, or a 1. If an order was created after 8am local customer time, BUT didnt ship until AFTER 5pm local customer time, it would be considered a failure or a 0. And to complicate things even more, if the order is created after 5pm local customer time, the order create time would be treated as it was created at 8am local customer time the following day local customer time, and would need to be shipped prior to 5pm local customer time that day. Would CASE be appropriate for this? Or am I thinking wrong? Any help would really be appreciated!!


Ordernum OrderCreate OrderShipDate TimeZ
67890 5/5/2005 11:575/6/2005 15:33 EST
35789 5/5/2005 13:575/5/2005 14:19 MST
44465 5/5/2005 13:58 5/5/2005 21:58 CST
87654 5/5/2005 18:00 5/7/2005 21:58 PST

View 13 Replies View Related

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)

View 4 Replies View Related

Calculating Dates

May 16, 2004

I have a table in my sql server that calculates renewal dates for me, that date is based on the final suit date. The table has the ssn, and the FinalSuitDate its in a one to many relationship with the employeetable.

the Finalsuit table is suppose to calculate the renewal dates(which I'm trying to do in a query) my original expression in access was using dateserial FirstRenewal: DateSerial(Year([FINALSUITDONE])+2,Month([FINALSUITDONE])+1,Day([FINALSUITDONE])=30), but sql does not recognize that.

For Example

If the Finalsuit is 12/01/2000
then the renewal would be 12/31/2002
the renewal are to be done on the last day of the month and two years from the finalsuit date. Problems is I'm having problems doing this in sql servers query?

Can someone out there help please

View 8 Replies View Related

Calculating Age In Years

Dec 19, 2001

--Use the following formula to calculate a person's age in years in a stored procedure,
--where @dob is the person's date of birth and @yyyymmdd is the date on which to determine the age:

DECLARE @age int
DECLARE @dob datetime
DECLARE @yyyymmdd varchar(11)

SELECT @dob = '12/06/1966'
SELECT @yyyymmdd = GETDATE()
SELECT @age = FLOOR(DATEDIFF(day, @dob, @yyyymmdd) / 365.25)
PRINT CONVERT(varchar, @age)

--Notes:
--Substitute "getdate()" for the @yyyymmdd variable if you want to determine the person's age right now.
--The function divides by 365.25 to allow for leap years and uses the FLOOR function to make sure the function returns an integer.
--The function DATEDIFF(year, @dob, @yyyymmdd) doesn't work because Microsoft chose to implement
--the "year" part of the function to calculate the number of year boundaries crossed.

View 20 Replies View Related

Calculating Between Tables

Nov 15, 2005

Hi All,

I have a very complicated project and for some reason I have decided to use SQL server as the backend database. I am new to it and have encountered my first problem. In basic terms what I want to be able to do is increment/decrement a field in a table according to something that happens in a different table. Where do I start?

So for example, a stereo system/cd player can have 10 cds at one particular time, when 2 cds are removed, then 8 slots are available. Then when 1 of the 2 remaining slots are filled then 1 slot remains and so on....so I want some sort of a counter field. Using that field I want to be able to generate a report on what is currently available.


Please Excuse my lame example but can anyone please help?

Thanks

Ben

View 1 Replies View Related

Calculating Interval

Mar 16, 2006

If two records containing datetime stamps are SELECTed, how can the interval between them be quickly calculated?

View 8 Replies View Related

Calculating Delays With Sql

Jul 11, 2006

I have three columns in my table,'StartDate',EndDate','BatchID'

When I upadate the the table for 'EndDate' I want to calculate the

I want to store this modified value somewhere and calculate the difference in (Value of 'End Date' Before update)-(Value of 'EndDate' after Update')

How it is possible?
Thanks in advance
Swati

View 1 Replies View Related

Calculating Duration

Jan 18, 2007

I am trying to get a query that will allow me to report the time taken to complete a certain training module.

The database itself does not have a duration field so I am tring to get the duration by using MIN and MAX. I can get the timing for when the module was opened and the time for the last mouse click on it, from this I need to be able to calculate the time taken to complete.

Query I am using to get the basic info comes from 3 tables so I have only attached the relevent output. Query used is as follow:

SELECT *
FROM PPS_SCOS, PPS_TRANSCRIPTS, PPS_TRANSCRIPT_DETAILS, PPS_PRINCIPALS
WHERE PPS_SCOS.SCO_ID = PPS_TRANSCRIPTS.SCO_ID
AND PPS_TRANSCRIPTS.TRANSCRIPT_ID = PPS_TRANSCRIPT_DETAILS.TRANSCRIPT_ID
AND PPS_TRANSCRIPTS.PRINCIPAL_ID = PPS_PRINCIPALS.PRINCIPAL_ID
AND PPS_SCOS.NAME LIKE 'MTM-106 The Dangers of Smoking'
AND PPS_PRINCIPALS.NAME LIKE 'Nigel Cordiner'
AND PPS_TRANSCRIPTS.TICKET NOT LIKE 'l-%'
ORDER BY PPS_TRANSCRIPT_DETAILS.DATE_CREATED

Output:

pps_scospps_scospps_transcript_detailspps_principalspps_principals
SCO_IDNAME DATE_CREATED PRINCIPAL_ID NAME
136850MTM-106 The Dangers of Smoking08:17:2516287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:17:2516287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:17:4016287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:18:2516287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:18:5716287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:19:1416287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:19:4716287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:20:2116287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:20:4416287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:21:2616287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:22:1316287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:24:5516287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:25:1216287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:25:2916287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:26:4916287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:27:0216287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:27:2916287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:27:4316287Nigel Cordiner



Have added the column heading and the tables the output comes from.

Relatively new to SQL so any help would be greatly received.

View 4 Replies View Related

Calculating Percentages For Each Row

Dec 10, 2007

One more for you while I'm trying to remember my SQL skill from a few years ago (last one of the day - promise):
My database looks like:

(Ignore that the ERD is in Access, the database is SQL Server 2005)

How do I calculate sales percentages?
I.E. how do I calculate the percentage that each product has sold as a percentage of the total sales?

What I'm aiming for is each row of products is shown, with its percentage of the whole sales.

AVG is involved this time, isn't it?

The productID is of the int datatype (not that you need to know that field - but never mind) and the quantity is a smallint, before you ask


Thank you in advance for any replies.


Paul

View 5 Replies View Related

Calculating In A Statement

Mar 3, 2008

Hi,
Im building a DB for a uni project and im having a little trouble creating a column that makes a calculation using 2 of the other columns.

I need to do this;
Select Reps, Weight, ((Reps / 30) + 1) * Weight AS [1RM]
From ......

But, SQL server 2005 doesnt seem to like my syntax and gives a wrong result.
Can anyone help?

Thanks,
M4tt

View 4 Replies View Related

Calculating A Percentage

Mar 25, 2008

My site has members. Each member has a certain goal, an amount that they are hoping to raise. Here's my tables:

---------------------
Members
---------------------
ID | Goal
---------------------


---------------------
Donors
---------------------
Amount | MemberID
---------------------


I'm trying to get the percentage of money raised compared to their goal. Basically 100*(SUM(Amount)/Goal). Here's my stored procedure:

CREATE PROCEDURE sproc_GetGoalPercentage
(
@memberid int
)
AS
SELECT(SUM(D.Amount)/M.Goal)*100
FROMMembers AS M INNER JOIN Donors AS D
ON M.ID = D.MemberID
WHEREM.ID = @memberid
GROUP BY M.Goal
RETURN


All it does is return 0.

View 7 Replies View Related

Calculating Row Sizes

Feb 20, 2008

I'm putting together a manual system that tracks data growth in a certain database. I was going to use sp_spaceused as a part of it, but then realized the datatypes for size are CHAR, not INT or BIGINT. I was going to do counts, averages, etc. on those columns but that wouldn't work against a CHAR field obviously. I could easily write a little something to strip out the KB, but was hoping there was another way to get those figures.

Secondly...has anynoe seen a stored procedure/code/etc. that just calculates the largest/smallest/average row size for a table? I haven't been able to find anything anywhere...

Any insight would be greatly appreciated...

Thanks!

View 2 Replies View Related

Calculating Business Day

Aug 20, 2007

Hi,

I am wondering what is vb.net method of calculating the prior business day (Monday - Friday).

For example, if today is Monday, Aug. 20, the last business day would have been Friday, Aug. 17.

I have a way to do this in SQL, but not vb.net.

Thanks much

View 4 Replies View Related

Calculating Distince

Jul 12, 2006

I have a table with only one column each for longitude and latitude. They will be passing the sa_property_id which has a long and lat listed. I need to show what else is withing 1 mile or 5 miles of that properties long/lat

--Table
CREATE TABLE [dbo].[PropertyDetails] (
[SA_PROPERTY_ID] [int] NOT NULL ,
[SA_X_COORD] [float] NULL ,
[SA_Y_COORD] [float] NULL ,
[Longitude] [float] NULL ,
[Latitude] [float] NULL ,
) ON [PRIMARY]
GO



Data output from above table

SASA_PROPERTY_ID Longitude Latitude
-------------- ----------------------------------------------------- -----------------------------------------------------
23790208 -120.619821 39.568587999999998

(1 row(s) affected)


Passed parameter = SA_PROPERTY_ID

Need list of matching records within 1 mile of above record

Would this be the best query for it? how do I define the radius?

I also have a function, but am not sure it can do what I need it to.


/*
* History: 14-Nov-05 CNH Simplified to resolve Divide By Zero problems
* 29-Mar-02 DKS Created by Deepak K Srinivasan
*
* Test Data:
*
* City Latt Long
* ---- ---- ----
* NYC 40.77 74
* SF 37.75 122.68
* Oakland 37.73 122.22
* Burbank 34.2 118.37
*/
CREATE FUNCTION dbo.CalculateDistance(@LatDeg1 FLOAT, @LonDeg1 FLOAT, @LatDeg2 FLOAT, @LonDeg2 FLOAT)
RETURNS FLOAT AS BEGIN
DECLARE @EarthRadiusMiles AS FLOAT,
@LatRad1 AS FLOAT,
@LonRad1 AS FLOAT,
@LatRad2 AS FLOAT,
@LonRad2 AS FLOAT,
@DotProd AS FLOAT

SET @EarthRadiusMiles = 3958.755 -- Volumic Radius of the Earth in Miles

SET @LatRad1 = RADIANS(ISNULL(@LatDeg1, 0.0))
SET @LonRad1 = RADIANS(ISNULL(@LonDeg1, 0.0))
SET @LatRad2 = RADIANS(ISNULL(@LatDeg2, 0.0))
SET @LonRad2 = RADIANS(ISNULL(@LonDeg2, 0.0))

SET @DotProd = SIN(@LatRad1) * SIN(@LatRad2) + COS(@LatRad1) * COS(@LatRad2) * COS(@LonRad1 - @LonRad2)

-- T-SQL provides ACOS. So, there is no need to implement it via ATAN:

RETURN @EarthRadiusMiles * ACOS(@DotProd)
END


View 1 Replies View Related

Calculating Next Reference

Feb 6, 2008

Hi,

Our tables have a reference field which users can enter their own references into.

However, they can choose to have the system calculate the next reference number.

The reference can contain any characters from a-z, A-Z and 0-9.

In our system, reference AAAA is greater than reference ZZZ; the next reference after AAAA is AAAB.

How would I calculate the greatest current reference in the table?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved