I want to read count(*) from various tables, I am trying code like that but won't work,
select count(*) as Table1.MyColumn1,
count(*) as Table2.MyColumn2
FROM Table1,
Table2
Any idea,
Thanks,
Hi I want to know what are the three different ways by which we can get the record cound for a particular table using SQL Query analyserThanks in advance
I am running a query showplan on a query. Can someone tell me how
to read what I am seeing? Or where I can get this information.
Thanks in advance,
Dianne
I have a report that is based on a query that is read from a .sql file and is executed in the stored procedure. I found this code on the internet. When I run the SP in Query Analyser it tells me that "2 rows have been affected". I don't know why I don't get the data. Any help??
Stored procedure:
CREATE PROCEDURE TimeTracker_Report_ClientNumbers2
AS
DECLARE @SQLQuery as NVARCHAR(4000)
BEGIN
CREATE TABLE #tmpQuery (Query NVARCHAR(4000))
BULK INSERT #tmpQuery FROM '\servernameQueriesReport_ClientNumbers.sql'
SELECT @SQLQuery = Query FROM #TMPQUERY
EXECUTE sp_executesql @SQLQuery
END
GO
and Report_ClientNumbers.sql contains the following query:
SELECT Name, ClientNumber FROM Companies WHERE Not ClientNumber IS NULL ORDER BY ClientNumber
The following query returns a value of 0 for the unit percent when I do a count/subquery count. Is there a way to get the percent count using a subquery? Another section of the query using the sum() works.
Here is a test code snippet:
--Test Count/Count subquery
declare @Date datetime
set @date = '8/15/2007'
select
-- count returns unit data
Count(substring(m.PTNumber,3,3)) as PTCnt,
-- count returns total for all units
(select Count(substring(m1.PTNumber,3,3))
from tblVGD1_Master m1
left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID
Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9
and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0
and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)
and v1.[Date] between DateAdd(dd,-90,@Date) and @Date) as TotalCnt,
-- attempting to calculate the percent by PTCnt/TotalCnt returns 0
(Count(substring(m.PTNumber,3,3)) /
(select Count(substring(m1.PTNumber,3,3))
from tblVGD1_Master m1
left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID
Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9
and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0
and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)
and v1.[Date] between DateAdd(dd,-90,@Date) and @Date)) as AUPct
-- main select
from tblVGD1_Master m
left join tblVGD1_ClassIII v on m.SlotNum_ID = v.SlotNum_ID
Where left(m.PTNumber,2) = 'PT' and m.Denom_ID <> 9
and v.Act = 1 and m.Active = 1 and v.MnyPlyd <> 0
and not (v.MnyPlyd = v.MnyWon and v.ActWin = 0)
and v.[Date] between DateAdd(dd,-90,@Date) and @Date
group by substring(m.PTNumber, 3,3)
order by AUPct Desc
Thanks. Dan
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;
What to know if it can be done.
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.
e.g
User ID, Downloads, Searches
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
View 4 Replies View RelatedI 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
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
Thanks For the help!
Hi ,
Is there anyway I can get to know the total no of tables on a database
Bindu
Hello,
Do anyone knows how can i count rows in all the tables in a database.
With select count(*) I can count rows only from one table .
TIA.
Hi all....
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'
--------------------------------------
This presently returns:
----------------------------------
ID(1)Project(t1) ID(2) Project(t2)
1 project_a 1 project_a
1 project_a 2 project_a
1 project_a 3 project_a
---------------------------------
What I need is to return only the title and a count from the second table of how many times the title occurs there.
Ultimately ....
Title[project_a] Count[3]
I am having difficulty with where to place the COUNT() within the select.
Any suggestions would be appreciated.
Thanks...
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.
Is there another way?
Hello To All @ Dev Shed,
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.
Hi,
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
Can any one tell me how to do it right?
Thanks.
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
2) CREATE TABLE [dbo].[tblProject](
[ProjectID] [int] IDENTITY(1,1) NOT NULL, [CAD] [bit] NULL, [RMS] [bit] NULL, [JMS] [bit] NULL,
and
CREATE TABLE [dbo].[tblProjectMilestones]([MilestoneID] [int] IDENTITY(1,1) NOT NULL, [ProjectID] [int] NULL, [ProjectSignedByCustomer] [datetime] NULL,
[ProjectCompleted] [datetime] NULL,
3) tblProject - CAD, RMS and JMS = True/False variations
tblProjectMilestones - ProjectCompleted = datetime
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:
Code:
CADCount | RMSCount| JMSCount|
5 10 3
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;
I have two tables
Books
-BookID
-CustomerID
Magazines
-MagazineID
-CustomerID
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:
CustomerID, BookCount, MagazineCount
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.
Many thanks.
Roger
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.
View 1 Replies View RelatedI 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.
View 1 Replies View Related
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.
Thanks,
Erik
If I have the following tables:
ID Car
1 Mazda3
2 Miata
Color Car ID
Black 1
Silver 1
Black 2
Style Car ID
i 1
s 1
touring 1
What is the simplest query that will return a result set like:
Car # of Colors # of Styles
Mazda3 2 3
Miata 1 0
I have the following code.
SELECT _bvSerialMasterFull.SerialNumber, _bvSerialMasterFull.SNStockLink, _bvSerialMasterFull.SNDateLMove, _bvSerialMasterFull.CurrentLoc,
_bvSerialMasterFull.CurrentAccLink, _bvSerialMasterFull.StockCode, _bvSerialMasterFull.CurrentAccount, _bvSerialMasterFull.CurrentLocationDesc,
_bvSerialNumbersFull.SNTxDate, _bvSerialNumbersFull.SNTxReference, _bvSerialNumbersFull.SNTrCodeID, _bvSerialNumbersFull.SNTransType,
_bvSerialNumbersFull.SNWarehouseID, _bvSerialNumbersFull.TransAccount, _bvSerialNumbersFull.TransTypeDesc,
[code]...
However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.
Anyone knows how to get a Table's Row Count from system tables?
Thanks.
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'
Table 1 HOLIDAY_REF
holiday_id | holiday_name
1 | Italy
2 | Russia
3 | Spain
Table 2 BOOKING
holiday_id | booking_status_id | total_value
1 | 330 | 2500
3 | 330 | 1500
1 | 330 | 1750
2 | 330 | 1240
2 | 330 | 5600
Results would be:
Holiday_id | holiday_name | total_value | record_count
1 | Italy | 4250 | 2
2 | Russia | 6840 | 2
3 | Spain | 1500 | 1
Not sure I'm going about it the right way.
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
View 4 Replies View RelatedHow can I get a quick count of all tables in a database? Is there a way to do this? Can the table count exclude system tables?
Hi,
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?
Thanks,
Tom
Help,
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];
using below script to compare two tables and get the values.
how to get the count of 'Table A' , 'Table B' , 'Table A & Table B' using below script.
Ex:
'Table A' -- 150
'Table B' -- 300
'Table A & Table B' -- 150
SELECT
Col1 = ISNULL(a.name,b.name),
Col2 =
CASE
WHEN ISNULL(a.name,'') = '' THEN 'Table B'
WHEN ISNULL(b.name,'') = '' THEN 'Table A'
ELSE 'Table A & Table B'
END
FROM #tableA a
FULL JOIN #tableB b
ON a.name = b.name;
I joined different tables and got a result like this:
result | process | goal | date |
------- ---------- ------ -----------
ok | process4 | 1 | 12.10.2013
bad | process1 | 2 | 13.10.2013
ok | process1 | 4 | 12.12.2013
good | process4 | 1 | 03.01.2014
ok | process1 | 3 | 10.04.2013
bad | process3 | 6 | 09.01.2014
bad | process4 | 3 | 30.12.2013
best |NULL| NULL
Now I want to count the results by counting the processes and group them by the result.
But it should be count the latest result per process only, e.g. for goal "1" just "good" at 03.01.2014. I solved that with a subquery (date=SELECT MAX(...)..).
But now the result "best" disappears, because that column has no date.
Secondly I want to count results for a specific process, e.g. for process4. Every goal has max. one process, with different dates. But one process could have more than one goal.
I want to have this result for process4:
count | result
------ -------
1 | good
1 | bad
0 | ok
0 | best
But I got only:
count | result
------ -------
1 | good
1 | bad
I have tried a lot, but nothing works.
The whole result (best, good, ok, bad) are stored in an other table and I joined it.