How Do I Join Two Tables To Get A Row Count?

Jan 10, 2007

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

View 3 Replies

Count On Inner Join Tables

Jul 23, 2003

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...

View 7 Replies View Related

Join Tables, Count Problems

Apr 19, 2006

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

View 7 Replies View Related

Join Tables Count Problems

May 8, 2006

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];

View 6 Replies View Related

COUNT Function And INNER JOIN On Multiple Tables

Jun 23, 2014

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

result of this query:

View 5 Replies View Related

How To Join 3 Tables Using Left Or Right Join Keyword?

Aug 17, 2007

Hi guys,

I'll appreciate any help with the following problem:

I need to retrieve data from 3 tables. 2 master tables and 1 transaction table.

1. Master table TBLOC contain 2 records :
rcd 1. S01
rcd 2. S02

2. Master table TBCODE contain 5 records:

rcd 1. C1
rcd 2. C2
rcd 3. C3
rcd 4. C4
rcd 5. C5

3. Transaction table TBITEM contain 4 records which link to 2 master table:
rcd 1. S01, C1, CAR

rcd 2. S01, C4, TOY
rcd 3. S01, C5, KEY
rcd 4. S02, C2, CAR

I use Left Join & Right Join to retrieve result below (using non-ASNI method) but it doesn't work.

Right Join method:

SELECT C.LOC, B.CODE, A.ITEM FROM TBITEM A RIGHT JOIN TBCODE B ON A.CODE = B.CODE

RIGHT JOIN TBLOC C ON A.LOC = C.LOC

GROUP BY C.LOC, B.CODE, A.ITEM ORDER BY C.LOC, B.CODE

When I use Non-ASNI method it work:

SELECT C.LOC, B.CODE, A.ITEM FROM TBITEM A, TBCODE B, TBLOC C

WHERE A.CODE =* B.CODE AND A.LOC =* C.LOC

GROUP BY C.LOC, B.CODE, A.ITEM ORDER BY C.LOC, B.CODE

Result:

LOC CODE ITEM
-----------------------------
S01 C1 NULL
S01 C2 NULL
S01 C3 CAR
S01 C4 TOY
S01 C5 KEY
S02 C1 NULL
S02 C2 CAR
S02 C3 NULL
S02 C4 NULL
S02 C5 NULL

Please Help.

Thanks.

View 3 Replies View Related

Using COUNT With JOIN

Feb 23, 2007

Hi people,
How do I count the number of rows in a table, where those rows relate to another table?
eg. using a JOIN with COUNT

View 3 Replies View Related

Join And Count

Apr 28, 2007

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.

View 28 Replies View Related

Count With Join

May 2, 2006

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

blog:
blogId
blogTitle
blogBody
WMid
blogNotes
blogCreated
blogModified

comments:
commentId
blogId
WMid
commentBody
commentDate

WM:
WMid
WMusername
WMpassword

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!

View 3 Replies View Related

COUNT(*) With A JOIN??

Mar 9, 2007

I am a newbie..and here is my newbie question

I have 2 tables : table1 and table2

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"

any help would be greatly appreciated! thx

View 3 Replies View Related

Get A Row Count Using An Inner Join

Apr 15, 2007

Hello,

I am trying to modify this query, I want to get the count of "reviews" that are related to this song. I added the follow line, but I get this error:

count(review.*) reviews,

Error:

Msg 102, Level 15, State 1, Procedure sp_wisetopic_artist_getAllMusic, Line 12
Incorrect syntax near '*'.

Here is the query:

CREATE procedure sp_wisetopic_artist_getAllMusic

@artistId int

as

select
Mp3.*,
Artist.[Name] ArtistName,
Album.*,
count(review.*) reviews,
Id3.bitRate,
Id3.frequency,
Id3.mode,
dbo.SecToMin(Id3.length) length

from wisetopic_mp3 Mp3

-- get artist info
left join wisetopic_artist Artist
on Mp3.userId = Artist.artistId

-- get album info (per song)
left join wisetopic_artist_album Album
on Mp3.albumId = Album.albumId

-- get id3 data (length)
left join wisetopic_mp3_id3 Id3
on Mp3.mp3Id = Id3.mp3Id

-- get review count
left join wisetopic_artist_song_review review
on Mp3.mp3Id = review.mp3Id

where Mp3.UserId = @artistId
order by Mp3.albumId

--select * from wisetopic_artist_song_review
--select * from wisetopic_mp3

I also tried this:
Review.count(*) reviews

Thanks

View 4 Replies View Related

Count Rows With Join

Jun 21, 2014

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"

Why isnt it doing what I want it to do?

View 4 Replies View Related

Need Help With Inner Join / Count On Sports Db

Nov 4, 2007

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

View 6 Replies View Related

Using Count On Both Sides Of A Join

Mar 13, 2008

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

View 5 Replies View Related

Compare Count Left Join?

Jul 10, 2014

I have the following tables.

Employee table

EmpNum, Department, Gender
100 AAA M
101 AAA F
102 BBB M
103 BBB F
104 AAA M
105 BBB F

EmpProducts table

EmpNum,Item
100 A1
100 A1
100 A2
101 A2
102 B1
102 B3
103 B2
104 A1
104 A2
105 B1

Products table

Deparment, Item, QtyM, QtyF
AAA A1 2 1
AAA A2 1 0
AAA A3 1 1
BBB B1 1 1
BBB B2 2 3
BBB B3 3 3

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

View 7 Replies View Related

Getting Count(*) From Left Side Of Join Using WHERE On Right Side Of Join

Jul 3, 2006

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

Quote:

View 2 Replies View Related

Simple Group By And Count With Join Not Matching Between Sql Server And Sql CE

Apr 2, 2007

In Sql Server

Code Snippet

CREATE TABLE t_contact

(

Id uniqueidentifier,

FirstName nvarchar(50),

LastName nvarchar(50),

TaskId uniqueidentifier

)

GO

CREATE TABLE t_task

(

Id uniqueidentifier,

Start datetime

)

GO

INSERT INTO t_task (Start, Id) VALUES ('3/25/2007 12:00:00 AM', '5949b899-3230-4d30-b210-9903015b2c6b')

INSERT INTO t_contact (FirstName, LastName, TaskId, Id) VALUES ('Adam', 'Tybor', '5949b899-3230-4d30-b210-9903015b2c6b', '304fc653-d366-404b-878d-9903015b2c6f');

INSERT INTO t_task (Start, Id) VALUES ('4/1/2007 12:00:00 AM', '4bd2df60-ca6c-493d-8824-9903015b2c6f')

INSERT INTO t_contact (FirstName, LastName, TaskId, Id) VALUES ('John', 'Doe', '4bd2df60-ca6c-493d-8824-9903015b2c6f', '7b91f7d6-d71e-47b4-a7ec-9903015b2c6f')

INSERT INTO t_task (Start, Id) VALUES ('3/29/2007 12:00:00 AM', '05167e74-cf63-452a-8f25-9903015b2c6f')

INSERT INTO t_contact (FirstName, LastName, TaskId, Id) VALUES ('Jane', 'Doe', '05167e74-cf63-452a-8f25-9903015b2c6f', '6871ee8d-bc83-478c-8a7c-9903015b2c6f')

GO

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

GO

Result (Expected)

2007-03-25 00:00:00.000 1
2007-03-29 00:00:00.000 1
2007-04-01 00:00:00.000 1

Result In Sql CE (UnExpected)

2007-03-25 00:00:00.000 3
2007-03-29 00:00:00.000 3
2007-04-01 00:00:00.000 3

Can SQL CE not count with a join? Seems like this a bug with aggregates or joins. I tried everything to try and get the correct result but no luck.

Thanks Adam

View 3 Replies View Related

Count From Two Tables

Jan 4, 2015

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;

View 2 Replies View Related

Count From Two Tables.

Oct 10, 2007

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

View 5 Replies View Related

Two Tables And Count

Jul 20, 2005

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 Related

Count From Multiple Tables

Jan 31, 2005

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!

View 2 Replies View Related

No.Of Tables Count....URGENT

Feb 14, 2000

Hi ,
Is there anyway I can get to know the total no of tables on a database

Bindu

View 2 Replies View Related

Count Rows In All Tables In A Db

Mar 1, 2001

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.

View 2 Replies View Related

Get Row Count From System Tables

Apr 22, 2002

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?

View 1 Replies View Related

SQL - Count, Sum + Group By On Three Different Tables.

Jan 18, 2005

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.

View 2 Replies View Related

Help In Joining Three Tables For COUNT

Jan 31, 2005

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.

View 2 Replies View Related

Count Date With 2 Tables?

Aug 18, 2012

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

View 1 Replies View Related

SQL 2012 :: Count From Two Tables

Jan 4, 2015

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;

View 9 Replies View Related

COUNT From Two Separate Tables

Apr 29, 2014

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

View 3 Replies View Related

How To Count Matches Between Two Tables?

Nov 24, 2007

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

View 6 Replies View Related

Record Count In All Tables

Jan 11, 2006

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 Related

Count All Records In All Tables

Apr 13, 2006

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.

View 1 Replies View Related

How To Count Across Multiple Tables In A DB?

Sep 12, 2007

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

View 1 Replies View Related