This is so complicated (for me) because I usually only work with single table and simple queries (SELECT, INSERT, UPDATE), but now I am in a situation where I am stuck.
What I am trying to archive is that: when a project manager logged-into his/her account, a grid-view will show a quick overview for all of his/her projects (id, created date, name and how many files are in pending) like below picture:
3 tables will be involved are:
Sample data for manager_id = 11
I tried this query but it not worked, it seems to display all columns right but the COUNT pending files column (assume the manager_id = 11)
SELECT COUNT(file_id) as 'Pending files', projects.project_id, projects.project_name, projects.status, projects.start_date
FROM ((project_manager
INNER JOIN files
ON project_manager.mag_id = files.manager_id AND project_manager.mag_id = 11 AND file_status = 'Pending')
INNER JOIN projects
ON projects.project_id = project_manager.project_id)
GROUP BY projects.project_id, projects.project_name, projects.status, projects.start_date
ORDER BY projects.status, projects.start_date DESC
I have two tables: Thread and Reply and they both have a field called UserID I need to know the number of rows in both tables where UserID="Chris" I can do this with two stored procedures and add the results together: SELECT COUNT(*) FROM Thread WHERE Thread.UserID='Chris' SELECT COUNT(*) FROM Reply WHERE Reply.UserID='Chris' but there must be a better way. Can this be written as one stored procedure with some sort of join? Thanks, Chris
Have a bit of a dilema involving getting a count on an inner join table select.
The projects (WU_Title) are listed in one table (BPI_Upload) and the second table (BPI_ProjectFeedback) holds multiple rows that are added via form where PF_Project is the same as WU_Title.
Here is the current select I am using... ---------------------------------------
SELECT t1.FileID, t1.WU_Title, t1.WU_Start, t1.WU_End, t1.WU_ProjectStatus, t2.PF_Project FROM dbo.BPI_Upload t1 INNER JOIN dbo.BPI_ProjectFeedback t2 ON t1.WU_Title = t2.PF_Project WHERE t1.WU_ProjectStatus = 'Completed'
I have a database that contains a column for UnitName , BeginDate andEndDate.I want to pass two parameters (@BeginDate and @EndDate) and retrieve atable of valuesthat include UnitName along with Counts for each UnitName.SELECT UnitName, COUNT(BeginDate) AS Start(SELECT COUNT(EndDate) AS Finish WHERE EndDate BETWEEN @BeginDate AND@EndDate)FROM TableWHERE BeginDate BETWEEN @BeginDate AND @EndDateGROUP BY UnitNameORDER BY UnitNameThis works. But when I try to add another count by using a subselect Iget an error dealing with GROUP BY not including the column in mysubselect.How is the best way to Count two columns using Group By.
I have the following problem: I want to join 2 tables but the table that I want to join on has duplicates of the same record.
This is what my query looks like: SELECT a.account, e.account AS Expr1, COUNT(e.ord_status) AS SentOrders, MONTH(e.datetime_added) AS Month, YEAR(e.datetime_added) AS YearFROM executionreports AS e INNER JOIN accounts AS a ON e.account = a.accountWHERE (e.ord_status = '0')GROUP BY a.account, e.account, MONTH(e.datetime_added), YEAR(e.datetime_added)ORDER BY Expr1 and the output looks like this:
1AA1AA328420061CC1CC45320061CD1CD8420061MA1MA1167320061MA1MA828420067TR7TR2420067TS7TS3696320067TS7TS2676420067TW7TW34420067TW7TW18320067UW7UW3320067VE7VE4320067YP7YP405320067YP7YP23142006TESTTEST142006 The problem is that the count is too high. This is because the account table has several entries with 1MA and 7TS for example. How can I correct it so i basically joins on a distinct set of the account table records?
I have 2 tables one called ProjectHeader and the other called ProjectSuppliers. There is a one to many relationships. I€™m trying to get a count of the total number of projects for a certain supplier where the Date Entered is less then 5/1/2206 with the Bid Date greater than 3/31/2206.
When I run the query below the count comes back wrong. I know it€™s because the ProjectSupplier table can have multiply entries of the supplier under the same Projectid. For example, the query below gives me a count of 206 projects for Miller in Tampa. Where he really only has 169 projects entered. I know you can run two queries to get the results. But because the data is being display on a Web page, I really would like the one SQL statement to handle this.
ProjectHeader Table
ProjectSuppliers Table
ProjectID
ProjectID
Bid Date
SupplierName
Date Entered
District
Modifed By
SELECT Count(ProjectHeader.ProjectID) AS CountOfProjectID, ProjectHeader.District, ProjectSuppliers.[Modifed By] FROM ProjectSuppliers INNER JOIN ProjectHeader ON ProjectSuppliers.ProjectID = ProjectHeader.ProjectID WHERE (((ProjectSuppliers.[Bid Date])>#3/31/2006#) AND ((ProjectSuppliers.SupplierName)='Some Supplier') AND ((ProjectSuppliers.[Date Entered])<#5/1/2006#)) GROUP BY ProjectHeader.District, ProjectSuppliers.[Modifed By] HAVING (((Count(ProjectHeader.ProjectID))>0)) ORDER BY Count(ProjectHeader.ProjectID) DESC , ProjectHeader.District, ProjectSuppliers.[Modifed By];
I have data like this in a two column temporary table -ID Age23586 323586 323586 223586 223586 123586 123586 123586 123586 1I need to create a temporary table that look like this:ID Age1 Age2 Age3 Age423586 5 2 2 0However, what I get is this:23586 5 NULL NULL NULL23586 NULL 2 NULL NULL23586 NULL NULL 2 NULLHere is the query that I am using...select managed_object_id, (select count(Age) where Age = 1) As Age1,(select count(Age) where Age = 2) as Age2,(select count(Age) where Age = 3) as Age3,(select count(Age) where Age = 4) as Age4into #enhancementCount from #enhancementsgroup by managed_object_id, AgeWhere's my mistake?Thanks-Danielle
COL1 | COL2 | COL3 | COL4 1 | FD | DR. A | Y 2 | FD | DR. A | Y 3 | FD | DR. A | N 4 | FD | DR. A | Y 5 | FD | DR. A | Y 6 | PF | DR. A | Y 7 | FD | DR. B | Y 8 | PF | DR. B | N
Consider the script below:
SELECT COL2, COL3, COUNT(COL1) AS TOTALS FROM CASES GROUP BY COL2, COL3 ORDER BY COL3, COL2
The script above produces the following output:
COL2 | COL3 | TOTALS FD | DR. A | 5 PF | DR. A | 1 FD | DR. B | 1 PF | DR. B | 1
I need to add one more column to the script that counts records with 'Y' in COL4 for each COL1 category (FD, PF). The final dataset would look like this:
COL2 | COL3 | TOTALS | NEWCOL FD | DR. A | 5 | 4 PF | DR. A | 1 | 1 FD | DR. B | 1 | 1 PF | DR. B | 1 | 0
I am having a hard time trying to use COUNT() on multiple columns with the GROUP BY restrictions that exist.
Hello, I am in the progress of designing a new section of my database and was thinking of creating a hole new database instead of just creating tables inside the database. My question is can you JOIN multiple tables in an SQL Statement from multiple databases. Ie, In the Management program I have a database called 'Convention' and another one called 'Services', inside the two databases there are many tables. Can I link say tblRegister from Convention to tblUser in Services? Thanks
A have a number of similar tables and what I want to do is to get the count of records grouped by day of week. All tables have date as an indexed unique column but the actual timestamps differs and have no relation. For one table I use this simple querry:
Select DatePart(dw,dato) AS DOW, Count(dato) AS NOR FROM AWP2 where dato > '2006-08-11' Group By DatePart(dw,dato)
Trying to join two or more tables in all sorts of variants of this:
Select datePart(dw,a1.dato) AS DOW1,Count(a1.dato) [Amount 1], datePart(dw,a2.dato) AS DOW2, Count(a2.dato) [Amount 2] FROM AWP1 A1 Inner Join AWP2 A2 on datePart(dw,a1.dato) = datePart(dw,a2.dato) Where a1.dato > '2006-08-11' AND a2.dato > '2006-08-11' Group By datePart(dw,a1.dato), datePart(dw,a2.dato)
I have 4 tables One is a user table and the other three contain records for the users. They all have a USERNAME column I would like to get a count of records for each table grouped by USERNAME
My output would be: username,totalFrom1,totalFrom2,totalFrom3
Thank you in advance for your assitance. I am trying to write a query that will query multiple tables for the same column. All the tables have thsi column "szF11". I am wanting something similar to this:
Code Snippet SELECT count(ulID) FROM (dbo.F_ACCOU_Data UNION dbo.F_AGNCY_Data UNION dbo.F_APPEA_Data UNION etc.....) WHERE szF11 = ' '
Note: ulID is the name of a column that every table has and szF11 is also in every table.
Pseudo Code: I want to count how many ulID's (if there is a row then something is in the ulID column it is never blank) in all the tables that are listed that have a blank in the szF11 column.
I am getting a very cryptic error message and of course I can't find anything in the documentation to help me understand the error.
I'm trying to get the number of records from one table where a column matches another column in a 2nd table. I then need the total values of another column that it has selected.
SELECT HOLIDAY_REF].holiday_id, COUNT([BOOKING].booking_status_id) AS record_count COUNT([BOOKING].total_value) AS total_value FROM [HOLIDAY_REF] LEFT OUTER JOIN [BOOKING] ON [HOLIDAY_REF].holiday_id = [BOOKING].booking_status_id WHERE [BOOKING].holiday_id=[HOLIDAY_REF].holiday_id && booking_status_id = '330'
how to count from multiple tables. So currently I pull two separate reports to show the count of TaskIDs by customer state and another to show WrapID by Customer state for last month:
TaskID Count by Customer state Query: --------------------------------------- SELECT CU.CustomerState as 'State' ,Count (CM.TaskID) as 'CaseCount' From Customer CU LEFT OUTER JOIN ACN_CCPCaseManagementTask CM ON CU.CustID=CM.CustID
[code]...
I am wanting to add both these counts into a single report so i tried the follow query but the counts don't match to the reports I pull separately.
SELECT CU.CustomerState as 'State' ,Count (CM.TaskID) as 'CaseCount' ,Count (CW.WrapCodeID) as 'WrapCount' From Customer CU LEFT OUTER JOIN ACN_CCPCaseManagementTask CM ON CU.CustID=CM.CustID LEFT OUTER JOIN acn_ccpwrapcode
select scheme.opheadm.order_no, scheme.porecpm.order_no, delivery_no, invoice_no, scheme.opheadm.customer, qty_received from scheme.opheadm join scheme.porecpm on (ltrim(rtrim(scheme.porecpm.commnt)) like (ltrim(rtrim(scheme.opheadm.order_no)) + '/%')) where effective_date between '2011-10-01 00:00:00.000' and '2011-10-08 00:00:00.000'
It gives me the 5 rows that I need to work with, one column is customer (which is giving me customer code) that I want to replace with customer name from another table
So I tried:
select scheme.opheadm.order_no, scheme.porecpm.order_no, delivery_no, invoice_no, scheme.jcmastm.name, qty_received from scheme.opheadm join scheme.porecpm on (ltrim(rtrim(scheme.porecpm.commnt)) like (ltrim(rtrim(scheme.opheadm.order_no)) + '/%')) join scheme.jcmastm on scheme.opheadm.customer = scheme.opheadm.customer here effective_date between '2011-10-01 00:00:00.000' and '2011-10-08 00:00:00.000'
this works with the same 5 rows that i need but loops them through every customer from the table scheme.jcmastm giving me a total of 960 rows not just the 5 that i want to work with. why this is looping?
hi i user this join and i have the answer like this"
select u.userid, u.user_name, u.password, c.code_description as role_code, convert(varchar, u.expiry_date,101) as expiry_date, u.created_date, u.active from [usermaster] u inner join [codeMaster] c on 'SP'=c.code where u.userid = '2'
select u.userid, u.user_name, u.password, c.code_description as role_code, convert(varchar, u.expiry_date,101) as expiry_date, u.created_date, u.active, v.user_date from [usermaster] u inner join [codeMaster] c inner join [HRUser_developerlog] v on 'SP'=c.code or u.userid=v.inserted_id and v.operation='delete' where u.userid = '2'
but i am getting error.can any onre please help me and please give me query please
I have two tables, let's say "Main" and "Dictionary".
The Main table has several fields that point to records in the same dictionary table. Because of the multiple joins I couldn't get any results if I use an expression like:
SELECT Main.ID, Dictionary.Text AS Data1, Dictionary.Text AS Data2
FROM Main, Dictionary
WHERE Main.Data1 = Dictionary.ID AND Main.Data2 = Dictionary.ID
What kind of join expression should I use? I have to generate this expression programmatically, so it's quite important to keep it as simple as possible!
An EMPLOYEE will always have at least 1 SKILL but each SKILL may or may not have any SKILLOPTIONS. I do an INNER JOIN:
EMPLOYEE->SKILL->SKILLOPTIONS but I only get a record if there is actually a SKILLOPTION. I want a record with EMPLOYEE and SKILL even if there are no SKILLOPTIONS. In Oracle it is the (+) symbol in the WHERE statement in conjunction with the JOIN. Am new to this so I'm sure the answer is simple.
1 2015 ba1 137 HL EL Eco 2 2015 ba1 138 EL SL HS 3 2015 ba1 139 SL EL His
From this table i use to admit a student and select their choice of group simultaneously all the subjects associated with GROUP is save on another table.
Here is the TABLE 2 Structure and sample data:
table 2 (NAME - tblstudetail)
id studentID session course sub1 sub2 sub3
1 15120001 2015 ba1 EL SL HS 2 15120002 2015 ba1 HL EL Eco 3 15120003 2015 ba1 SL EL His 4 15120004 2015 ba1 HL EL Eco
AND so no..........................
Now i just want to COUNT the Number of Groups Filled in tblStudateil.
I have three tables, Users, DocType and Docs. In the DocType table there are multiple entries for allowed document types, the descriptions and other pertinent data. In the Docs table, there are all manner of documents. In the User table are the users.
The DocType and Docs tables are relational. DocType.ID = Docs.tID The Users and Docs tables are relational. Users.ID = Docs.uID
Every user is allowed to have exactly one document of each type. Therefore if there are 10 document types in the DocType table, there may be as many as 10 matching documents in the Docs table.
What I need is a single record for each user returning a boolean for each document type, whether or not there is a matching record in the Docs table.
For example, there are 5 document types defined in the DocType table (types 1 - 5), so the DocType table has 5 rows. In the Docs table, there are 23 rows, and in the User table there are 10 rows. Given that each user may have only one of each DocType, there could be a maximum of 50 rows in the Docs table, but there are 23, meaning that on the average each user is missing one document.Now the challenge is to return a table of all the users (10 rows) with a boolean value for each of the rows in DocType (as columns) based on whether there is a value in the Docs table that matches both the DocType and User.
I have 3 tables , Customer , Sales Cost Charge and Sales Price , i have join the customer table to the sales price table with a left outer join into a new table.
i now need to join the data in the new table to sales cost charge. However please note that there is data that is in the sales price table that is not in the sales cost charge table and there is data in the sales cost charge table that is not in the sales price table ,but i need to get all the data. e.g. if on our application it shows 15 records , the sales price table will maybe have 7 records and the sales cost charge table will have 8 which makes it 15 records
I am struggling to match the records , i have also tried a left outer join to the sales cost charge table however i only get the 7 records which is in the sales price table. see code below
I have two tables a and b, where I want to add columns from b to a with a criteria. The columns will be added by month criteria. There is a column in b table called stat_month which ranges from 1 (Jan) to 12 (Dec). I want to keep all the records in a, and join columns from b for each month. I do not want to loose any row from a if there is no data for that row in b.
I do not know how to have the multiple joins for 12 different months and what join I have to use. I used left join but still I am loosing not all but few rows in a, I would also like to know how in one script I can columns separately from stat_mont =’01’ to stat_month =’12’
/****** Script for SelectTopNRows command from SSMS ******/ SELECT a.[naics] ,a.[ust_code] ,a.[port] ,a.[all_qty_1_yr] ,a.[all_qty_2_yr]
[Code] ....
Output should have all columns from a and join columns from b when the months = '01' (for Jan) , '02' (for FEB), ...'12' (for Dec): Output table should be something like
* columns from a AND JAN_Cum_qty_1_mo JAN_Cum_qty_2_mo JAN_Cum_all_val_mo JAN_Cum_air_val_mo JAN_Cum_air_wgt_mo JAN_Cum_ves_val_mo FEB_Cum_qty_1_mo FEB_Cum_qty_2_mo FEB_Cum_all_val_mo FEB_Cum_air_val_mo FEB_Cum_air_wgt_mo FEB_Cum_ves_val_mo .....DEC_Cum_qty_1_mo DEC_Cum_qty_2_mo DEC_Cum_all_val_mo DEC_Cum_air_val_mo DEC_Cum_air_wgt_mo DEC_Cum_ves_val_mo (FROM TABLE b)
I am rather new to SQL 2005 and I am currently facing a problem when I attempt to sum the income and expenses which requires joining 2 tables. My sql string that is currently having a problem is listed below:
I USED THIS STATEMENT:
SELECT TblIncome.Recieve, SUM(TblIncome.Amount) AS TotalIncome, SUM(TblExpenses.Amount) AS TotalExpenses FROM tblincome,tblexoenses WHERE tblincome.recieve=tblexpenses.paymentto GROUP BY TblIncome.Recieve
AND I ALSO USED THIS STATEMENT:
SELECT TblIncome.Recieve, SUM(TblIncome.Amount) AS TotalIncome, SUM(TblExpenses.Amount) AS TotalExpenses FROM TblIncome INNER JOIN TblExpenses ON TblIncome.Recieve = TblExpenses.paymentTo GROUP BY TblIncome.Recieve
The problem that occurs is it sums and multiplies the number of records in the second table. For example,
TblIncome:
Recieve Amount John 5 John 2 David 1
TblExpenses:
PaymentTo Amount John 3 David 2
Currently, it is showing me as follows:
Recieve TotalIncome TotalExpenses John 7 6 David 1 2
The problem is in total expenses, it multiplies the number of records in TblIncome.
Also, if TblExpenses has more than 1 record, it multiplies the total income with the number of records.
Does anyone know how to solve this problem? Thanks
I'm trying to join 3 tables in an outer join since I am loosing records that need to be included if I only use an inner join. I am pulling data from an MSDE database using the microsoft query tool.
The problem is that I get the message that I can't use an outer join on a query with more than 2 tables, but that can't be right can it?
I'm a SQL code novice so any help would be greatly appreciated!
SELECT Article.articleId , Article.articleName , Article.articleStatus , Articlegroup_2.ArticlegroupId , Articlegroup_2.g2_key , Articlegroup_2.g2_name , articleGroup.articleGroupId FROM HIP.dbo.Article Article, HIP.dbo.articleGroup articleGroup, HIP.dbo.Articlegroup_2 Articlegroup_2 WHERE articleGroup.articleGroupId = Article.articleGroupId AND Article.articleGroupId2 = Articlegroup_2.Articlegroup_2_Id
I'm trying to join 2 tables in an outer join, but MS Query won't let me do this because I have another 2 tables included in an inner join ("only two tables are allowed in an outer join"). I am pulling data from an MSDE database using the microsoft query tool.
I'm a SQL code novice so any help would be greatly appreciated!
Here is my existing SQL query (without the new outer join table):
I am trying to pull in columns from multiple tables but am getting an error when I run the code:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "a.BOC" could not be bound.
I am guessing that my syntax is completely off.
SELECT b.[PBCat] ,c.[VISN] --- I am trying to pull in the Column [VISN] from the Table [DIMSTA]. Current Status: --Failure ,a.[Station] ,a.[Facility] ,a.[CC] ,a.[Office]
create multiple INNER JOIN on derived tables as I have written below or use a #temp table for all derived tables and use them into JOIN. This below query is also very hard to understand what is going on .
I am trying to select specific columns from multiple tables based on acommon identifier found in each table.For example, the three tables:PUBACC_ACPUBACC_AMPUBACC_ANeach have a common column:PUBACC_AC.unique_system_identifierPUBACC_AM.unique_system_identifierPUBACC_AN.unique_system_identifierWhat I am trying to select, for example:PUBACC_AC.namePUBACC_AM.phone_numberPUBACC_AN.zipwhere the TABLE.unique_system_identifier is common.For example:----------------------------------------------PUBACC_AC=========unique_system_identifier name1234 JONES----------------------------------------------PUBACC_AM=========unique_system_identifier phone_number1234 555-1212----------------------------------------------PUBACC_AN=========unique_system_identifier zip1234 90210When I run my query, I would like to see the following returned as oneblob, rather than the separate tables:-------------------------------------------------------------------unique_system_identifier name phone_number zip1234 JONES 555-1212 90210-------------------------------------------------------------------I think this is an OUTER JOIN? I see examples on the net using a plussign, with mention of Oracle. I'm not running Oracle...I am usingMicrosoft SQL Server 2000.Help, please?P. S. Will this work with several tables? I actually have about 15tables in this mess, but I tried to keep it simple (!??!) for the aboveexample.Thanks in advance for your help!NOTE: TO REPLY VIA E-MAIL, PLEASE REMOVE THE "DELETE_THIS" FROM MY E-MAILADDRESS.Who actually BUYS the cr@p that the spammers advertise, anyhow???!!!(Rhetorical question only.)
I have to join two tables and i need to fetch All records from @tab2 and only max date record from @tab1 that ID is present in Tab2
1.) @Tab1 have multiple records for each ID
2.) @Tab2 also have multiple records for each ID
3.) Kind of Lef Outer join those tables with ID and take all records from @tab2 and only Max of date from @tab1 and order by ID and Date
Note: @Tab1 always have lesser dates than @tab2 for each ID
Tables looks like as followsÂ
declare @tab1 table (id varchar(3), effDt Date, rate int) insert into @tab1 values ('101','2013-12-01',5) insert into @tab1 values ('101','2013-12-02',2) insert into @tab1 values ('101','2013-12-03',52)
[code]....
In the given ex, ID 103 should not come as it is not present in @tab2, ID 104 should come even it is not present in @tab1 as we ahve to use left outer join Result should like follows.
i have database which has 25 tables. all tables have productid column. i need to find total records for product id = 20003 from all the tables in database.
I want to count the rows in the Incident Table by using filters to limit the rows to be counted if they meet the below conditions. I know I need a logical test for each row of the incident table based on the apparatus table’s rows. But, I want to test for each row in the incident table, counting, but not returning a true or false in the overall measure.Something like look at each incident row, test for true or false and then count IF the statement is true. Then go to the next incident row and do the same. The aggregation would be the final count of “true” results.I tried this for MET objective:
=CALCULATE(COUNTROWS(incident), Â Â Â Â Â Â Â apparatus[Incident Response Time] >-1 || Â Â Â Â Â Â Â apparatus[Incident Response Time] <320, Â Â Â Â Â Â Â uv_901APP_TYPE[Description]="Engine", Â Â Â Â Â Â Â uv_901INCIDENT[Top_Category]="Fire"
I have a sql query that I am using to populate a datagrid. The problem is one of the tables is a month table. and the other tables are full of data. So there is no common column name to match using a inner join "on".
