Doing Summation On Multiple Criterias On The Same Column In A Singlequery
Mar 24, 2008
Hi all
I need to perform a summation on a column of a table based on a
criteria given in another column in the same table. The catch is i
need to perform different sums according to the number of criterias in
the criteria column, in a single query. the structure of the table is
somethinmg like this (only relevant columns are shown)
TABLE1
Value - numeric(20,6)
Month - int
indicator - bit
Now i need to do something like:
SELECT Month, SUM(Value) FROM TABLE1
WHERE indicator = 1
GROUP BY Month
and also
SELECT Month, SUM(Value) FROM TABLE1
WHERE indicator = 0
GROUP BY Month
How can i do this in a single query, something like this:
SELECT Month, SUM(Value where indicator=1), SUM(Value where
indicator=0) and so on .......
I am not sure if I wrote the question's title appropriatly or not.....but anyways I wud try to explain the problem.
I am facing a problem solving the following task in MS Access (I believe it can surely be solved in access or SQL server).I am posting a sample table format here for reference and also postin the expected result table there-after.
The problem goes like this.(Read Carefully)
I have a table which contains a payment history with one row per account (each account number is unique) and related payment history for each account.
where Dt is date and P1/2/3/... is payment.There cud be 1 or more than 1 payments in a particular month as shown.
The original file has more than 200 columns for the payment i.e till Dt200,P200
I need to see aggregated monthly payment history for all accounts. i.e. a table for all accounts with related payments for each month (its OK if a day vary in particular month...consolidation shud be monthly) starting from the earliest to the latest possible.
I hope I have tried to explain the problem in as much detail as possible.
Please help me with your valuable solutions to the above task ASAP.If u want i can also send in as attachment the original file i am workin at to ur email id
can anyone help me about reporting services on how to sum the coloumn and put it on a textbox... but the case is like this,
Stud Name Fee Name Fee Amt. Amt Paid John Doe Registration 1,000 1,000 Tuition Fee 5,000 3,500 1,500 Jane Bake Tuition Fee 5,000 5,000
SubTotal 16,000 11,000
when i aggregate the Fee Amt. using Sum function i get the result of 16,000 because john doe break his payment in to two(it should be 11,000 only). any help would be appreciated.
I've a stored procedure which retrieves based on different criterias. I added one more critieria - to display a column based on a range of values. The values are @OriginalMin and @OriginalMax. I declared the variables and gave the conditions. But still set ANSI_NULLS ON set QUOTED_IDENTIFIER OFF GO ALTER procedure [dbo].[USP_Account_Search_Mod]@OriginalMin DECIMAL=0 ,@OriginalMax DECIMAL=0 AS DECLARE @CRI8 VARCHAR(500)SELECT @CRI1='' SET @CRI8='AND OriginalBalance >=@OriginalMin AND OriginalBalance<=@OriginalMax' SELECT @Criteria = ......+ @CRI8 When I execute this stored procedure, I get the following error message. SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(3)) AND Customer = '00001'AND OriginalBalance >=@OriginalMin AND OriginalBalance<=@OriginalMax UNION SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(3)) AND Customer = '00001'AND OriginalBalance >=@OriginalMin AND OriginalBalance<=@OriginalMaxORDER BY NAME ASC Msg 137, Level 15, State 2, Line 1 Must declare the variable '@OriginalMin'. Msg 137, Level 15, State 2, Line 1 Must declare the variable '@OriginalMin Could someone tell what's wrong with the procedure? For convenience, I've included only the latest critieria I added.
How can I get the sum of the query results of these formula:
SELECT dbo.TechphonesCalibQA.ID, dbo.TechphonesCalibQA.EmpID, dbo.TechphonesCalibQA.EmpName, dbo.TechphonesCalibQA.[Level], dbo.TechphonesCalibQA.Coach, dbo.TechphonesCalibQA.Queue, dbo.TechphonesCalibQA.PreferredName, dbo.TechphonesCalibQA.SesID, dbo.TechphonesCalibQA.SesDate, dbo.TechphonesCalibQA.SesTime, dbo.TechphonesCalibQA.SesLen, Case dbo.TechphonesCalibQA.G_Branded when dbo.TechphonesCalibClient.G_Branded then 1 else 0 end as G_Branded, Case dbo.TechphonesCalibQA.G_PIVer when dbo.TechphonesCalibClient.G_PIVer then 1 else 0 end as G_PIVer, Case dbo.TechphonesCalibQA.G_AskMemb when dbo.TechphonesCalibClient.G_AskMemb then 1 else 0 end as G_AskMemb, Case dbo.TechphonesCalibQA.G_AskCall when dbo.TechphonesCalibClient.G_AskCall then 1 else 0 end as G_AskCall, Case dbo.TechphonesCalibQA.G_UsedSend when dbo.TechphonesCalibClient.G_UsedSend then 1 else 0 end as G_UsedSend, Case dbo.TechphonesCalibQA.V_Privacy when dbo.TechphonesCalibClient.V_Privacy then 1 else 0 end as V_Privacy, Case dbo.TechphonesCalibQA.V_Followed when dbo.TechphonesCalibClient.V_Followed then 1 else 0 end as V_Followed, Case dbo.TechphonesCalibQA.V_Changed when dbo.TechphonesCalibClient.V_Changed then 1 else 0 end as V_Changed, Case dbo.TechphonesCalibQA.D_UseEffect when dbo.TechphonesCalibClient.D_UseEffect then 1 else 0 end as D_UseEffect, Case dbo.TechphonesCalibQA.D_ConConfi when dbo.TechphonesCalibClient.D_ConConfi then 1 else 0 end as D_ConConfi, Case dbo.TechphonesCalibQA.D_PropDoc when dbo.TechphonesCalibClient.D_PropDoc then 1 else 0 end as D_PropDoc, Case dbo.TechphonesCalibQA.D_UseKb when dbo.TechphonesCalibClient.D_UseKb then 1 else 0 end as D_UseKb, Case dbo.TechphonesCalibQA.S_Fix when dbo.TechphonesCalibClient.S_Fix then 1 else 0 end as S_Fix, Case dbo.TechphonesCalibQA.S_Recorded when dbo.TechphonesCalibClient.S_Recorded then 1 else 0 end as S_Recorded, Case dbo.TechphonesCalibQA.S_Provided when dbo.TechphonesCalibClient.S_Provided then 1 else 0 end as S_Provided, Case dbo.TechphonesCalibQA.C_Askaddit when dbo.TechphonesCalibClient.C_Askaddit then 1 else 0 end as C_Askaddit, Case dbo.TechphonesCalibQA.C_Stated when dbo.TechphonesCalibClient.C_Stated then 1 else 0 end as C_Stated, Case dbo.TechphonesCalibQA.AB_PresntInfo when dbo.TechphonesCalibClient.AB_PresntInfo then 1 else 0 end as AB_PresntInfo, Case dbo.TechphonesCalibQA.AB_ResMbrTym when dbo.TechphonesCalibClient.AB_ResMbrTym then 1 else 0 end as AB_ResMbrTym, Case dbo.TechphonesCalibQA.AB_Respond when dbo.TechphonesCalibClient.AB_Respond then 1 else 0 end as AB_Respond, Case dbo.TechphonesCalibQA.AB_Appsol when dbo.TechphonesCalibClient.AB_Appsol then 1 else 0 end as AB_Appsol, Case dbo.TechphonesCalibQA.AB_Adjustsp when dbo.TechphonesCalibClient.AB_Adjustsp then 1 else 0 end as AB_Adjustsp, Case dbo.TechphonesCalibQA.AB_respect when dbo.TechphonesCalibClient.AB_respect then 1 else 0 end as AB_respect, Case dbo.TechphonesCalibQA.AB_MuteOcur when dbo.TechphonesCalibClient.AB_MuteOcur then 1 else 0 end as AB_MuteOcur, Case dbo.TechphonesCalibQA.AB_HoldOcur when dbo.TechphonesCalibClient.AB_HoldOcur then 1 else 0 end as AB_HoldOcur, Case dbo.TechphonesCalibQA.SS_actlist when dbo.TechphonesCalibClient.SS_actlist then 1 else 0 end as SS_actlist, Case dbo.TechphonesCalibQA.SS_empathy when dbo.TechphonesCalibClient.SS_empathy then 1 else 0 end as SS_empathy, Case dbo.TechphonesCalibQA.SS_focus when dbo.TechphonesCalibClient.SS_focus then 1 else 0 end as SS_focus, Case dbo.TechphonesCalibQA.SS_confi when dbo.TechphonesCalibClient.SS_confi then 1 else 0 end as SS_confi, Case dbo.TechphonesCalibQA.SS_commun when dbo.TechphonesCalibClient.SS_commun then 1 else 0 end as SS_commun, Case dbo.TechphonesCalibQA.SS_accent when dbo.TechphonesCalibClient.SS_accent then 1 else 0 end as SS_accent
FROM dbo.TechPhonesCalibClient INNER JOIN dbo.TechphonesCalibQA ON dbo.TechPhonesCalibClient.SesID = dbo.TechphonesCalibQA.SesID
I have 2 tables ZIPCROSS and HOUSEHOLDS. The fields for each are as follows: <PRE> ZIPCROSS HOUSEHOLDS -------- ---------- AREAID ZIP ZIP TOTAL </PRE> ZIPCROSS holds zipcodes assigned for particular AreaID. HOUSEHOLDS contains TOTAL number of household in each zipcode.
Now, I need to build a query that returns SUM of TOTAL for a given AREAID grouped by SCF (first 3 numbers of the zipcode) and SUM of TOTAL for a given SCF. Thus the results should look something like this: <PRE> AREAID SCF TOTAL SCFTOTAL ------ --- ------- --------- 1 900 1234 43210 1 901 2345 54321 </PRE> etc... I can write a query that can get the right TOTAL or the right SCFTOTAL but not both on one query. The following query gives me the right SCFTOTAL but not TOTAL.
SELECT A.AREAID, LEFT(C.ZIP,3) AS SCF, SUM(D.TOTAL) AS TOTAL, SUM(E.TOTAL) AS SCFTOTAL FROM AREAORDER A JOIN ZIPCROSS C ON A.AREAID=C.AREAID JOIN HOUSEHOLDDATA D ON C.ZIP=D.ZIP JOIN HOUSEHOLDDATA E ON LEFT(C.ZIP,3)=LEFT(E.ZIP,3) WHERE A.MAILINGORDERID=133 GROUP BY A.AREAID, LEFT(C.ZIP,3) ORDER BY A.AREAID, SCF
I'm aware of why this doesn't work but I can't seem to find the right approach. Any solutions? TIA.
I have some data -- counts ID'd by location and grid East like this --Loc East NCA 100 3CA 103 5CA 109 2CA 110 3I'm interested in the total of N on either side of the largest gap inEastings.In this case the largest gap is 6 (between 103 and 109), and the sum ofN for the 2 rows below the gap is 8, and for the 2 above the gap it's5.The problem is to locate the largest gap, and compute the sum of N forthe cases on either side. There are multiple locations, multipleEastingsper location, but only one largest gap. (If there are two largestgaps, itdoes't matter which one is used for the sums.)I can do this with multiple passes -- first locate the largest gap,then goback and locate the Eastings on either side, then sum up the Ns.That'srealy clumsy, I can't figure out how to do it more quickly, and I'm notsurewhat I'm doing is right. Any help would be appreciated.Thanks,Jim Geissman
I am attempting to do a simple summation statistics calculation with T-SQL to count rows based on an int column FK relationship to another table.
I have two tables: (1) document, and (2) filespec. Both tables have an int primary key column and some miscellaneous columns. Filespec has an int field which is keyed to the primary key of document (FK constraint), thus, each document can have zero-to-many file specifications.
Example:
Document Table: DocID Name 1 Approved Plan 2 Photo Gallery
Filespec Table: FileID DocumentID Name 1 2 First Photo 2 2 Second Photo
Expected Result: DocID FileCount 1 0 2 2
How to generate this result? Thanks in advance for advice.
I have been toiling over getting this query to run properly for a while now and finally had to turn to the community for help. We are trying to develop a query that will show us an idea of customer loyalty at the store. It basically takes the transactions for a specific user from one table, and matches that customer ID to the customer table so we can also select names and phone numbers. The query runs great, but it shows every single transaction that a customer has done between our specified time range. We want to be able to have the query add each transaction and total it for us so we have just one customerID with one total instead of say one customerID with 19 transactions. Any ideas? Our current query is below:
select customer.accountnumber, customer.firstname, customer.lastname, xaction_total From Customer, xaction
where xaction_time between '01/1/2006' and '12/31/2006' and Customer.ID = xaction.CustomerID and customer.accountnumber not like 'X%' order by customer.accountnumber desc
This is probably a simple problem for most but I'm a little confused on it.
I have a table containing shifts (ShiftStart (DateTime), NumHours (INT)). Now if I have a job scheduled to start at day S and run for X hours. I want to determine the day it would end on. I can do this using a cursor but am looking for a cursor-less solution if possible. Anybody have any thoughts.
I am trying to update a parent table with a summation of its child records. The child records are being deleted because the transaction has become invalid because payment was made with a bad check or there was a posting error. So a rollback of sorts is required.
Here are is the DDL for the tables and DML for the data:
Code: DECLARE @t1 TABLE ( [Year] int NOT NULL, [Parcel] varchar(13) NOT NULL, [InterestDateTime] datetime NULL, [Principal] decimal(12, 2) NULL, [Penalty] decimal(12, 2) NULL,
[Code] ....
I tried to use a Merge statement with an ON MATCH for each TransType, but it complained that I could not have multiple update statements. OK. So I tried a MERGE with single update statement with a case and it complained that I was updating the same parent multiple times, which I was and want to! So, I tried the following update statement and it still does not work, though no error message.
Code: update t1 set t1.Principal = t1.Principal + (case when t2.TransType = 'R' then t2.Payment else 0 end), t1.Penalty = t1.Penalty + (case when t2.TransType = 'P' then t2.Payment else 0 end), t1.Interest = t1.Interest + (case when t2.TransType = 'I' then t2.Payment else 0 end) from @t1 t1 inner join @t2 t2 on t2.YEAR = t1.YEAR and t2.Parcel = t1.Parcel
I need to update multiple columns in a table with multiple condition.
For example, this is my Query
update Table1 set weight= d.weight, stateweight=d.stateweight, overallweight=d.overallweight from (select * from table2)d where table1.state=d.state and table1.month=d.month and table1.year=d.year
If table matches all the three column (State,month,year), it should update only weight column and if it matches(state ,year) it should update only the stateweight column and if it matches(year) it should update only the overallweight column
I can't write an update query for each condition separately because its a huge select
I have created a single FULLTEXT on col2 & col3. suppose i want to search col2='engine' and col3='toyota' i write query as
SELECT
TBL.col2,TBL.col3 FROM
TBL INNER JOIN
CONTAINSTABLE(TBL,col2,'engine') TBL1 ON
TBL.col1=TBL1.[key] INNER JOIN
CONTAINSTABLE(TBL,col3,'toyota') TBL2 ON
TBL.col1=TBL2.[key]
Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.
I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information. for e.g.; i was thinking to concatinate both fields like col4= ABengineBA + ABBToyotaBBA and in search i use SELECT
TBL.col4 FROM
TBL INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1 ON
TBL.col1=TBL1.[key] Result = 1 row
But it don't work in following scenario col4= ABengineBA + ABBCorola ToyotaBBA
SELECT
TBL.col4 FROM
TBL INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1 ON
TBL.col1=TBL1.[key]
Result=0 Row Any idea how i can write second query to get result?
This is a report I'm trying to build in SQL Reporting Services. I can do it in a hacky way adding two data sets and showing two tables, but I'm sure there is a better way.
TheTable Order# Customer Status
STATUS has valid values of PROCESSED and INPROGRESS
The query I'm trying to build is Count of Processed and INProgress orders for a given Customer.
I can get them one at a time with something like this in two different datasets and showing two tables, but how do I achieve the same in one query?
Select Customer, Count (*) As Status1 FROM TheTable Where (Status = N'Shipped') Group By Customer
I have a stored proc I am updating in an OLEDB Command from the results of a Transform Script Component. The Stored Proc has over 65 input parameters, most of them have a NULL passed in. I dont want to create output columns in the Transform Script Component for all of them to map them from the "Available Input Columns" to "Available Destination Columns".
I want to create 3 or 4 generic Output columns for their data type - say IntegerOutput (datatype Int), DateTimeOut (datatype datetime) and so on. The I want to map these generic columns in the OLEDB Command as Available Input Columns" to multiple "Available Destination Columns" - wherever the datatype matches the input column.
But the OLEDB Command Column Mappings let me map One to One only. This will create a huge and unnecessary workload for me to develop and maintain - when I tell you I have 3 such stored procedures, all of whose interfaces are exactly same and for which I can create similar Output columns in the Transform Script Component.
I have a SQL Query issue you can find in SQL Fiddle
SQL FIDDLE for Demo
My query was like this
For Insert Insert into Employee values('aa', 'T', 'qqq') Insert into Employee values('aa' , 'F' , 'qqq') Insert into Employee values('bb', 'F' , 'eee') Insert into Employee values('cc' , 'T' , 'rrr') Insert into Employee values('cc' , 'pp' , 'aaa') Insert into Employee values('cc' , 'Zz' , 'bab') Insert into Employee values('cc' , 'ZZ' , 'bac') For select select col1,MAX(col2) as Col2,Max(Col3) as Col3 from Employee group by Col1
how to declare multiple derived columns in SSIS Derived Column Task in one attempt.as i have around 150 columns coming from Flat file. I had created the required Expression in Excel and now i want add those in derived column task but its allowing only 1 expression at a time.
I need to query out multuple rows of data from the following table and retrieve it as a single row. Ideally, I'd like to do some sort of subquery that supports multiple columns, but as far as I know, that only exists on Oracle, not MSSQL. Here's an example of the data:UserDefinedFieldId UserDefinedRowID FieldValue1 1 date (stored as text)2 1 text3 1 text1 2 date (stored as text)2 2 text3 2 text...27 UserDefinedFieldIds for each row in the actual table I wrote a working query, but I need something more efficient for this particular application. (The query to do this must fit in a 2000char data field--with all 27columns brought in with separate subqueries, I'm pushing 5000.) Already tried shorter table names, which only saved me about a 1000 characters. SELECT (SELECT SecondaryTable.FieldValue FROM UserDefinedData AS SecondaryTable WHERE SecondaryTable.UserDefinedRowID = PrimaryTable.UserDefinedRowId AND SecondaryTable.UserDefinedFieldId = 1) AS Column1, ,,, (SELECT SecondaryTable.FieldValue FROM UserDefinedData AS SecondaryTable WHERE SecondaryTable.UserDefinedRowID = PrimaryTable.UserDefinedRowId AND SecondaryTable.UserDefinedFieldId = 27) AS Column27,FROM UserDefinedData AS PrimaryTableWHERE PrimaryTable.UserDefinedFieldId = 5 AND Cast(Cast(PrimaryTable.FieldValue AS varchar(64)) AS datetime) > GetDate() ORDER BY PrimaryTable.UserDefinedRowID, PrimaryTable.UserDefinedFieldId Any suggestions would be appreciated. Thanks, Brad
I have two tables, one of which is a key table with a subaccount number and a set of attributes that define that subaccount. I am trying to join this key table with the table with all the attributes and come up with one table of subaccounts. The Subaccounts should only lookup the attributes associated with them, not all of the attributes, so I put OR [attribute] IS NULL in the WHERE clause so it only matched on the appropriate columns. This worked great in an initial test with two attributes but when I put all 9 attributes in it crashed with this message "Msg 415, Level 16, State 1 The current query would require an index on a work table to be built with 15 keys. The maximum allowable number of keys is 16" like this CREATE VIEW subaccounts (Subacct_no, balance) AS SELECT (s.Account_No + "." + r.Subacct_Ext, S.Balance) FROM Acct_Rcd r, Subacct_Key s WHERE (s.attr1 = r.attr1 OR s.attr1 IS NULL) AND (s.attr2 = r.attr2 OR s.attr2 IS NULL)
I'm putting together the site for a local darts league, essentially each of the teams completes an 'e-scorecard' for that particular game. This is then stored in a table named 'scorecardIndex' :
So each 'index' points off to the UID of the player who was captain, and the UID of of each team.
What I'm struggling with is joining this all together, as I'm joining both scxTeam1Capt and scxTeam2Capt to the same columns in the 'players' table (plFirstName, plSurname)
with cte1 as (select siteid ,productcategory ,sum(isnull(netsales,0)) as netsales from dbo.vw_sv_invoicedetail where invoicedate>=dateadd(dd,-90,getdate())
[Code] ....
I need to add routeid column so that i can finally display sum of sales grouped by routeid in addition to the siteid that i am already displaying.
I am trying to query multiple columns for a specific value. I have 8 columns (values are either 1 or 0)and I want to query the table to find out which rows contain zero's in ALL of the 8 columns. Whats the best way to do this? I can create a lenghty select statement where column1 =0 and column2 =0 and column3=0 and column4 =0 and column5 =0 .... etc. I was wondering if there was an easier way to do this?
I'm trying to write a query which allows that multiple values from onecolumn are placed in one record.ex:tableNrLetters1A2A2B2C3A3B3C3D3E4AThe result I want to get from an select:NrAll Letters1A2A, B, C3A, B, C, D, E4AOlivier
We are trying to create a multiple column report. The data looks like this:
Department PersonName Department PersonName Department PersonName Department PersonName
So sample data would be:
Advertising Mary Marketing Harry Marketing Tim Marketing Zoe Sales Joe Sales Sue ...
The report needs to look like this:
Advertising Mary
Marketing Harry Tim Zoe
Sales Joe Sue
So basically the header of the department needs to span 2 columns and the people need to be printed in 2 columns under that header.
This is a common layout for many of our reports (group header spans multiple columns of child data) - however we cannot figure out how to do this with reporting services. We were able to do this in other reporting tools, but are trying to move to reporting services.
Any idea on how to do this in reporting services?
The only idea I have seen, which is not acceptable is to modify the incoming data into something like:
Department Person1 Person2 Department Person1 Person2 Department Person1 Person2
This is especially bad because if the report format changes you now have to change the way the data is sent in.
Any ideas on how to accomplish this with the data coming in properly?