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 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 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?
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
If we insert a value into fieldname of one table.I should get the value into the same fieldname of second table and i want the count of another fieldname of second table in a gridview. Can we write a single sql statement to join two tables and to count the fieldname of the second table.
Hello m'dears... As you may have gather I need help...
I have three tables (blog/comment/WM) the blog lists the entries into the blog i'm creating whereas the comments table holds comments user have made to the blog entries... The WM table holds the usernames of the users... the schema looks like this
I want to list all blog entries with a JOIN to the WM table to show the username but i also want to COUNT the amount of comments made for each entry...
I'm looking at it and it SEEMS really simple but i can't get my head around it and time's running out any help or pointing out a topic i missed that covers this will be most appreciated
We're all going to hell... I guess I'll see you there!
SELECT table1.cache_type, table2.log_owner_id FROM table1, table2 WHERE table1.cache_id = table2.cache_id and table2.log_owner_id=62 and table1.cache_type = "traditional"
This returns 10 rows. But what I would like to do is return the total number of rows..the COUNT.
I tried the following but that does not work: SELECT COUNT(*) table1.cache_type, table2.log_owner_id FROM table1, table2 WHERE table1.cache_id = table2.cache_id and table2.log_owner_id=62 and table1.cache_type = "traditional"
This is my sql string. It counts all the rows in Questions table but it should only count the rows where id in Quizzes matches the quiz column in Questions table.
"select Quizzes.name, Quizzes.id, count(Questions.quiz) as total from Quizzes inner join Questions on Quizzes.id=Questions.quiz"
I'm very new to this so I will start from scratch and would love any advice from anyone who is more knowledgable than I.
So I have two tables, Game_Schedule and Standings
Relevant columns in Game_Schedule: Team1 (Represents home team) Team2 (Represents away team) Win ('true' represents win for home team) Loss ('true' represents win for away team)
Relevant column in Standings: T_Name Wins T_Tier
THE OBJECTIVE is to count the number of wins (true values in 'Game_Schedule') for each team in 'Standings' and list them ordered by T_Tier and then Wins.
Sounded easy at first but I haven't had any success.
Here is my LATEST ATTEMPT:
SELECT (Select DISTINCT standings.T_Name from standings), Game_Schedule.Team1, standings.T_Tier, Game_Schedule.Team2, Game_Schedule.Win, Game_Schedule.Loss, (Select Count(*) FROM Game_Schedule WHERE (standings.T_Name=Game_Schedule.Team1 AND Game_Schedule.Win=1) OR (standings.T_Name=Game_Schedule.Team2 AND Game_Schedule.Loss=1)) AS Win_Counter FROM standings, Game_Schedule ORDER BY T_Tier ASC, Win_Counter DESC
Here is an INNER JOIN attempt that also did not work:
SELECT DISTINCT T_Name, T_Tier, Wins, Game_Schedule.Team1, Game_Schedule.Team2, Game_Schedule.Win, Game_Schedule.Loss FROM standings INNER JOIN Game_Schedule ON standings.T_Name=Game_Schedule.Team1 OR standings.T_Name=Game_Schedule.Team2 ORDER BY Game_Schedule.Win DESC
The query probably speaks for itself. You can see that I had to use a sub select to get the last column. It seems like there must be a better way, but we could not find it. We are on sql 2000. below is the query is the first few rows of the the result set. This query works, being new to sql I am just curious if the sub select could have been avoided in this case. Thanks!
SELECT count(distinct a.store)as [# Stores doing at least 1 Verified Deposit], b.division, b.district, (select count(store_no) from store where district = b.district and fiscal_year = 2008 and current_status = 'a' and owner ='company')as '# of Stores in District' FROMdepositvariance a, store b WHERE a.verified_date >'10/25/2007' and a.store = b.store_no and b.fiscal_year = 2008 and b.current_status = 'a' and b.owner ='company' Group BYb.division, b.district
# Store that did at least 1 Verified Deposit division district # of Stores in District -------------------------------------------- -------- -------- ----------------------- 42 200 201 44 28 200 202 40 38 200 203 45
Each employee need to have a specific amount of product that is assigned to the employees department I need to know if they have to little or to many. The result should be like this.
100 have the correct items for the gender and department so he should not be in the result.
101 A2 1 She is 1 over the 0 she should have. 102 B2 -2 102 B3 -2 103 B1 -1 103 B2 -2 103 B2 -3 104 A1 -1 104 A3 -1 105 B2 -3 105 B3 -3
I made this SQL but i got stock in the count.
Select ep.item, e.Empnum, p.item from employee e INNER JOIN products p on p.department = e.department LEFT JOIN EmpProducts ep on e.Empnum = ep.EmpNum and p.items = ep.item
here I need the count of the specific item and compare it against the QtyM if gender is male and against QtyF if gender is female
where EmpProductCount <> ProductsQty order by Empnum
Hi,I have 2 tables: tblStatements and tblLines (one to many) AnytblStatements record can have many associated records in tblLines.The search criteria is against tblLines (ie tblLines.fldDateofService
SELECT task1_.Start as y0_, count(this_.FirstName) as y1_ FROM t_contact this_ inner join t_task task1_ on this_.TaskId=task1_.Id GROUP BY task1_.Start
I have two tables. I have three columns DEPARTMENT_NAME, LAST_NAME, JOB_ID.
DEPARTMENT_NAME is from table
1. JOB_ID and LAST_NAME are from table 2.
I would like to count the number of employees for each DEPARTMENT_NAME and I want to be displayed DEPARTMENT_NAME, LAST_NAME, JOB_ID. But all I can do is to find the number of employees for each job_id. One DEPARTMENT_NAME has more than one JOB_ID.
Here is my code: SELECT E.JOB_ID, D.DEPARTMENT_NAME, COUNT(E.LAST_NAME) FROM EMPLOYEES E JOIN DEPARTMENTS D ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID) GROUP BY E.JOB_ID, D.DEPARTMENT_NAME;
Have a main table with the user id's in it it and i want to join it to two other tables. One being a downloads table and the second being a searches tables, will join them both via the user id's. What i would like to do is count the rows from both the and searches tables and return each value next with userid.
I have two tables in my DB for an e-commerce app. This allows for 1 order tohave multiple products. I want to select the OrderID from the Orders tablebut only when all the corresponding records in the OrderDetails table allhave their Despatched value = 0. Can anyone offer any help please?Simplified tables below.Orders-------------OrderIDUserIDOrderNumberOrderDateOrderDetails-------------OrderDetailsIDOrderIDProductIDDespatched
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
Does anyone know how to get the row count of a user table by using the system tables. There is no guarantee that these user tables will have any indexes - so I can not use the sysindexes table to count the rows in a clustered index.
I have three tables, say, A, B, and C. Both B and C contain/reference the primary key of A, ie B.a_id, and C.a_id. Multiple rows in B and multiple rows in C can have the same a_id. C and B have no relationships and essentially independant of each other.
I'm trying to find a single query (to prevent having to rewite function interfaces) that can return all the fields of A, the sum of a feilds in B, B.cost, where B.a_id = A.a_id and finially the count of C.a_id 's for where C.a_id = A.a_id.
The query I've been worlking on so far is as follows,
SELECT A.name, A.a_id, SUM (B.cost), COUNT ( DISTINCT C.a_id) FROM A LEFT JOIN B USING (a_id) LEFT JOIN C USING (a_id) GROUP BY A.a_id
This produces the correct result for the count of C.a_id (thanks to a DISTINCT) but the sum of B.cost is out by the factor of count C.a_id. I can see why this is happening but have completely run out of ideas. Group by on two columns may be? A strategically placed subquery? Is is even possible?
Any help would be much appreciated. Thanks in advance for your time.
Peter
PS I'm new to this forum thing so if you want the unsimplified query with all the full names just let me know and I'll post it.
I have three tables - clients, messages, orders. clientid is the primary key for clients and clientid is present in both messages and orders as foreign key. I want to get clientid, messagecount, and ordercount, that is, for each client I want to get number of messages posted by the client and number of orders placed by the client.
To get clientid, messagecount I do this:
select a.clientid, count(b.clientid) as messagecount from clients a left outer join messages b on a.clientid = b.clientid group by a.clientid
To get clientid, ordercount I do this:
select a.clientid, count(b.clientid) as ordercount from clients a left outer join orders b on a.clientid = b.clientid group by a.clientid
But I am not able to get all three clientid, messagecount, ordercount in a single query. This is what I tried. But it is not giving me the correct answer.
select a.clientid, count(b.clientid) as messagecount, count(c.clientid) as ordercount from clients a left outer join messages b on a.clientid = b.clientid left outer join orders c on a.clientid = c.clientid group by a.clientid
I'm starting with the question but my end would be to store the results of this data so I can query it from my Access front end using between dates to determine totals.
1) How do I create a SQL view/temp table to query count totals from an MS Access 2010
4) To calculate the count for each deliverable with no dates: Select (Select COUNT (*) as CountCAD from tblProject where CAD='true') as CADNumber, (Select COUNT (*) as CountRMS from tblProject where RMS='true')as RMSNumber
5) When I run the query between any dates, using tblProjectMilestonses.ProjectCompleted, what I would like is:
I have two tables. I have three columns DEPARTMENT_NAME, LAST_NAME, JOB_ID.
DEPARTMENT_NAME is from table 1. JOB_ID and LAST_NAME are from table 2.
I would like to count the number of employees for each DEPARTMENT_NAME and I want to be displayed DEPARTMENT_NAME, LAST_NAME, JOB_ID. But all I can do is to find the number of employees for each job_id. One DEPARTMENT_NAME has more than one JOB_ID.
Here is my code:
SELECT E.JOB_ID, D.DEPARTMENT_NAME, COUNT(E.LAST_NAME) FROM EMPLOYEES E JOIN DEPARTMENTS D ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID) GROUP BY E.JOB_ID, D.DEPARTMENT_NAME;
How would i write a single sql statement where i can get that counts how many bookIDs are listed for each custoemrID and how many magzaineIDs are listed for each customerID and have it return one table that looks like this:
Hello. I'm quite new to SQL so have included as many details as i can think of here. The scenario is a wordsearch style puzzle. The user can select their answers in any order and these answers are stored in a table. I need help with the UPDATE statement to compare the users answers against the correct answers for that puzzle. (Note: In the actual scenario there will be 10-15 answers per grid, but i have reduced the number to make testing easier - hopefully the code for a working UPDATE statement will be scalable to account for grids with different numbers of answers etc.)
The Tables: -- These are the correct answers for a given grid (gridid). -- Due to the nature of the puzzle, answers can be in any order. -- Each level may contain one,two,or no 'bonusanswers' which are harder to find, so these are scored separately so bonus points can be awarded. CREATE TABLE correctanswers ( gridid smallint IDENTITY(1,1)PRIMARY KEY CLUSTERED, answer1 char(15), answer2 char(15), bonusanswer1 char(15), bonusanswer2 char(15) )
-- These are the user submitted set of answers 'answerid' for level 'gridid'. -- Answers may be submitted in any order. CREATE TABLE useranswers ( answerid smallint IDENTITY(1,1)PRIMARY KEY CLUSTERED, gridid smallint, firstanswer char(15), secondanswer char(15), thirdanswer char(15), fourthanswer char(15), )
-- A user (userid) submits their answers which get stored as answerid. -- This table shows the scores for each set of answerid's the user has submitted. -- A high score table for both the individual user, and all users may be created using this table. CREATE TABLE userscores ( userid smallint, answerid smallint, mainmatches smallint, bonusmatches smallint )
The Test Data: -- sample test data -- 2 users userid's '1' and '2' each have two goes on level1 (gridid 1)
-- correct answers for gridid 1 INSERT INTO correctanswers (answer1, answer2, bonusanswer1, bonusanswer2) VALUES ('cat','dog','rabbit','elephant')
-- user submitted answers for gridid 1 INSERT INTO useranswers (gridid, firstanswer, secondanswer, thirdanswer, fourthanswer) VALUES (1,'dog','rabbit','horse','cow') INSERT INTO useranswers (gridid, firstanswer, secondanswer, thirdanswer, fourthanswer) VALUES (1,'dog','cat','elephant','horse') INSERT INTO useranswers (gridid, firstanswer, secondanswer, thirdanswer, fourthanswer) VALUES (1,'rabbit','cat','elephant','donkey') INSERT INTO useranswers (gridid, firstanswer, secondanswer, thirdanswer, fourthanswer) VALUES (1,'horse','cat','dog','sheep')
-- scores for users attempts - columns 3 and 4 needs calculating INSERT INTO userscores VALUES (1,1,null,null) -- one main answer and one bonus answer, so null,null should be 1,1 INSERT INTO userscores VALUES (1,2,null,null) -- two main answers and one bonus answer, so null,null should be 2,1 INSERT INTO userscores VALUES (2,3,null,null) -- one main answer and two bonus answers, so null,null should be 1,2 INSERT INTO userscores VALUES (2,4,null,null) -- two main answers and no bonus answers, so null,null should be 2,0
I have included the correct new table values for the sample data - basically filling in the two null fields in the 'userscores' table.
I haven't used SQL much but from the little i know then i think the answer will include JOIN, COUNT and IN statements as part of the UPDATE statement...but i haven't a clue where to start with the order/logic etc.
I have looked for sample solutions myself but all the examples i have found so far are to do with exact matches between two tables, whereas in my scenario i need to know how many matches, irrelevant of the order.
There a stored procedure or query that will count all the recordsin all my tables in a database.One of these two -exec sp_MSforeachtable N'SELECT "table" = ''?'', cnt = COUNT(*) FROM ?'select object_name(id), rows from sysindexes where indid in (1, 0)Is there a way to add columns to the second query? I would like to seehow many columns and their names as well.Thanks.
I need some help with this. I was able to count all the records in ourdatabase using the user_tables and user_tab_columns tables afterrefreshing the statistics on this database.We are doing an upgrade of a system and I will not be able to refreshthe statistics during the upgrade. I need more of a manual process ofrunning these queries.Now I do:select A.table_name, round(A.num_rows,0) as rowcount,count(b.table_name) as ColumnCountfrom dba_tables A, dba_tab_columns Bwhere A.table_name = B.table_name and A.owner in ('PS','SYSADM')group by A.table_name, A.num_rowsorder by rowcount desc, columncount descBut I can't use the num_rows anymore so I was thinking more to do this:Select A.table_name from(select count(*) from A.Table_name B where A.Table_name =B.Table_Name)from user_tableThis does not work for me since I don't know how to pass the table_namefrom the first select to the second select. The logic is there but thesyntax is not.Please help.
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.
