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.
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 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.
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.
Hi allI need to perform a summation on a column of a table based on acriteria given in another column in the same table. The catch is ineed to perform different sums according to the number of criterias inthe criteria column, in a single query. the structure of the table issomethinmg like this (only relevant columns are shown)TABLE1Value - numeric(20,6)Month - intindicator - bitNow i need to do something like:SELECT Month, SUM(Value) FROM TABLE1WHERE indicator = 1GROUP BY Monthand alsoSELECT Month, SUM(Value) FROM TABLE1WHERE indicator = 0GROUP BY MonthHow can i do this in a single query, something like this:SELECT Month, SUM(Value where indicator=1), SUM(Value whereindicator=0) and so on .......Could any body please help me on this ?
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
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 am receiving funny results from a query. To simplify, I have 2 tables (todayyesterday). Each tbl has the same 8 columns. My query joins the two tables then looks where either of two columns has changed. What is happening is that when checking one of the columns it seems as though sql is flipping the column, causing it to be returned in error.
result set
colA colB colC colD colE colF colG colG (from yesterday) 1 1 a b c d e m 1 1 a b c d m e
So what's happening is that the record above is actually the same record and should not be returned. There is a daily pmt column that changes but I am not using that in the query. Aside from that the two records are identicle.
I have the following situation (with a site that already works and i cannot modify the database architecture and following CrossRef tables -- you will see what i mean by CrossRef tables below)
foreach hotel, there definitely is a crossRef entry in AddressCrossRef and Address tables respectively (since every hotel has an address)
however not all hotels have thumbnail image
hence i have hotel inner join AddressXReff inner join Address ..... however i must have left outer join mediaXref left outer join media
the problem is that if there is no entry in Media or mediaXref, I don't get any results
i tried to get over it by using where (media.mediaTyple like 'thumbnail' or media.mediaType is null) but then i started getting multiple results for each hotel because media's of type movie or full_image or etc... all got returned
...when I started this endeavor. I have a previously developed Lotus Notes App. The idea was simple; as I am sefl taught on Lotus Script, I figured I'd be able to stumble my way through VB.Well, it started OK. I used VB Express to get familiar with the stuff, but decided to go with a full version of VS 2005 and try and get this thing properly developed as a web app. I purchased several reference books etc., and have become relatively familiar with the forums here.First issue I have is that I simply want to use code to update or add records to an SQL DB. I know about datagridview etc., but I want to update the DB using forms, not the tabular view those controls provide. I thought it would be relatively straight forward, but found my ignorance runs deeper than I thought. When I tried to do so I am finding I am not really clear on where to make declarations etc. in the web app. If anyone could point me in the right direction that would be great. The issue with searching these forums is most posts deal with datagridview or something similar.I have spent a ton of time trying to find relevant posts or articles, but have had no luck yet.Again, all Ireally need is a nudge in the right direction. I am more than willing to plod through reference materials or articles/posts to find what I need to know, I just can't find where to even start on the info I need. Regards, Joe
Hi I have a problem with my sql WHERE query, if i manually type ([Area] = 'The First Area') then it is okay but if i try and pass the variable 'The First Area' using the
([Area] = @Area) it doesnt work. ALTER PROCEDURE dbo.StoredProcedure1(@oby nvarchar,@Area char,@Startrow INT,@Maxrow INT, @Minp INT,@Maxp INT,@Bed INT)ASSELECT * FROM(SELECT row_number() OVER (ORDER BY @oby DESC) AS rownum,Ref,Price,Area,Town,BedFROM [Houses] WHERE ([Price] >= @Minp) AND ([Price] <= @Maxp) AND ([Bed] >= @Bed) AND ([Area] = @Area)) AS AWHERE A.rownum BETWEEN (@Startrow) AND (@Startrow + @Maxrow) Please Help I know it must be something simple as the sql works but not when i pass the variable.... Thanks In Advance
Output: First row: initial values of the fields Second row: average of the same fields
Please help me...
select * from ( select HEM_LOKOSIT, HEM_NNS from LPMS.HEMOGRAMS where HEM_PATIENT_ID = 33 union select AVG(HEM_LOKOSIT), AVG(HEM_NNS) from LPMS.HEMOGRAMS where HEM_PATIENT_ID = 33) order by HEM_LOKOSIT desc nulls last;
I'm relatively new to SQL7 but I did use 6.5 a fair bit. I'm trying to test the restore of the Transaction log backup and having a bit of difficulty. The idea is that I make a complete database backup at 1am backup the transaction log every 30 minutes between 7am and 7pm. I need to be able to restore the database to a known state between 7am and 7pm with a max data loss of 30 minutes.
What I am trying to achieve is (as a test):
1)Create a small test database with a test table 2)Add some data to the test table 3)Back up the transaction log 4)Restore the transaction log to 'undo' the data added in step 2
Should be simple I think !!! The problem I am encountering is that in step 4 it won't let me restore only the transaction log (a tick automatically appears in the database backup as well). Bah !
Can someone please tell me what simple steps are required to get this to work. I need specifically on what options to chose during the backup and restore processes.
Hi, I have a table with two columns. I need to find distinct value of col1 and the correspondin repeated value of col2 for that col1 value with comma seperated list. Is there any function for this in MS SQL? I need somethgn like a 1,2,3 b 4,5 c 7 d 5,55,5
I can do that with creating 2 cursors but looking for some easy way around.
DELETE FROM #RptDetails WHERE StructureType <> @StructureType AND #RptDetails WHERE #RptDetails.TraderId <> @TraderId OR #RptDetails.TraderId is null
But it didnt delete the structure types i changed to : DELETE FROM #RptDetails WHERE StructureType <> @StructureType --AND DELETE FROM #RptDetails WHERE #RptDetails.TraderId <> @TraderId OR #RptDetails.TraderId is null
and it did, how do i format the 2nd sql into 1 statement and what was i doing wrong?
I think it's simple, but I can't get it to work.In English its: find records in TableA where the field [Field1] hasmore than one unique value in Field2sample records in TableAField1 Field22241 123452241 123452242 123452242 99856desired return (2 records)2242 123452242 99856thank you for your helpPaul
I have a form that after being filled out has its contents written to adatabase and then goes to confirmation .asp page. I want the confirmationpage to be personalized but I am not sure how to pass the "name" parameterto the confirmation.asp page. I know I am going to kick myself whrn I seethis.Thanks, Houston
I'm trying to find out if a particular column in a table has more than 10 digits. for example: the TN column. How can I write this query? The data type for this column is bigint. Also, would it be better to change this data type to char(10) instead as it's used primarily for phone # lookups? Thanks in advance!
Hi, Im trying to create a simple search page to return a list of products from a table in a sql database. Id like to be able to search a product description column in a table I have called products using the contents of a text box. I was wondering how i can go about getting the search to check for all words the user types in rather than just a single word or an exact phrase. Im currently using the following sql query SELECT [product_title], [product_description] FROM [products] WHERE ([product_description] LIKE '%' + @product_search + '%') this works fine for single words and exact phrases but if i had product called 'fred w bloggs' and i enter 'fred bloggs' it will not return anything. Please could anyone suggest how i shoud go about this? Im not sure if my web hosting company will enable full text search or will this be required? Thanks for any help! pete_ (very new to asp.net!)
hay friends scene is that i wana read single multiple rows of a single column from a sql database and then want to shows those values in text box,,, so plz tell me ho to do it. By using data set ,,data table or what to use for this and how.... wll be waiting for ur coordination
Hi; I know it must be really easy but I couldn't somehow figure it out: 3 columns: pms_ID, pms_UserID, pms_FlagSeen 1 ab1 True 2 ab1 True 3 ab1 True 4 ab1 False 5 ab2 True 6 ab2 False All I want is to count number of Falses and Total (Trues and Falses) belonging to Users (I mean by pms_UserID) Any help would be appreciated.. Regards...
