A Q About Counting
Jan 28, 2007
Hi,
New to SQL. Got some questions about it.
Suppose I have two tables. Each of them has a single column, named as
c1. For table T1, I have:
1
1
1
3
3
5
7
9
For table T2, I have:
1
2
3
4
5
1
3
The exercise I want to do is to select the number of occurence in T1
for those elements in T2. For above tables, I want to show:
1 3 ( i.e. "1" is in T2 and shows 3 times in T1)
2 0 (i.e. "2" is in T2 but doesn't show in T1)
3 2 (i.e. "3" is in T2 and show 2 times in T1)
It seems I can't figure out a good way to do this. Any help will be
appreciated.
Thanks
View 2 Replies
ADVERTISEMENT
Jun 26, 1998
When I have made a query with IDC, how can I make a counter in the .htx-file, which tells me if there was no query-results ?
View 2 Replies
View Related
Oct 24, 2007
I am working on writing a query to use with Reporting Services where I am pulling grades from a school grade book trying to see what students qualify for Honor Roll.
I have one Honor Roll done but am still working on the other. The qualifications I am trying to get is that a student has a gpa >3.0 and can have one C grade. I've got the 3.0 and the C's down but how to get the query to pull anyone having at the most one C. It doesn't matter whether it is C+, C, or C- but the student can't have more than a count of one.
Here is what I have
SELECT DISTINCT s.lastname+','+' '+s.firstname AS Student, s.studentnumber,e.grade,t.name AS Term,
gs.score, c.name AS Course, se.teacherdisplay
FROM GradingScore gs
INNER JOIN student s ON s.personid = gs.personid
INNER JOIN v_TermGpa tg ON tg.personid = s.personid
INNER JOIN enrollment e ON e.enrollmentid = s.enrollmentid AND e.calendarid = gs.calendarid
INNER JOIN scheduleStructure ss ON ss.calendarid = gs.calendarid
INNER JOIN Termschedule ts ON ts.structureid = ss.structureid
INNER JOIN Term t ON t.termscheduleid = ts.termscheduleid
INNER JOIN section se ON se.sectionid = gs.sectionid
INNER JOIN course c ON c.courseid = se.courseid
WHERE gs.calendarID = 20 AND t.name ='1st 6wk' AND tg.term1gpa >=3.0 AND s.enddate IS NULL
AND gs.score <='C-'
AND (select gs1.personid, gs1.score
from gradingscore gs1
inner join enrollment e1 on e1.personid = gs1.personid
Inner join schedulestructure ss1 on ss1.calendarid = e1.calendarid
Inner join termschedule ts1 on ts1.structureid = ss1.structureid
Inner join term t1 on t1.termscheduleid = ts1.termscheduleid
where gs1.score IN('C','C+','C-') and (count(score)= 0 or count(score) < 2))
ORDER BY s.[student]
I can get it to work all the way up to where the And (count(score) starts then there is something about the way the count is in there that I haven't figured out yet.
Thanks in Advance for any help or advice
View 9 Replies
View Related
Jan 26, 2008
Hi,
How do I count the total number of each row in a table? The table Company has a field called Group. How do I count the total amount of members in each group?
Thanks in advance!
View 23 Replies
View Related
Oct 5, 2007
Hi all,
I would like to have my SQL statement result to return an additional "column", automatically adding an "auto-increasing" number with it.
So if I for example select all Dates older than today's date, I would want something like this:
1
10/12/2006
2
10/18/2006
3
10/20/2006
4
10/22/2006
5
10/30/2006
Keep in mind that it's not my intention to fysically insert the "counting" column into the table, but rather do it "virtually".
Is this possible? And if yes, how ? :)
Thanks in advance
Nick
View 6 Replies
View Related
Jan 31, 2008
Sorry for all the code below. I am realizing that my DB design is bad but I already have 7 pages built around it that work fine...until now, so I would really like to not change the DB if possible. My table has 22 columns: iID which is the identity colum. Then there is iAsmtID which is the assessment ID. Lastly there are 20 colums- q1 through q20, each of which will have a 1, 2, or 3, depending on the radio buttons the user clicked. Nows my problem. I have to find a percent for the assessment. It works like this. 3s are NA so we are not worried about them now. I need to find the number of ones and the number of twos for each assessment ID. Then add those together and divide by the number of ones. How can I find the number of the ones and twos. I have below but its not working. Says there is incorrect syntax at the ',' which is a different color below. Any and all help appreciated.'Open connectionset conn=Server.CreateObject("ADODB.Connection")conn.open My_Connset rs = Server.CreateObject("ADODB.Recordset")str = "SELECT SUM((CASE WHEN q1=1 THEN 1 ELSE 0 END)+(CASE WHEN q2=1 THEN 1 ELSE 0 END)+(CASE WHEN q3=1 THEN 1 ELSE 0 END)+(CASE WHEN q4=1 THEN 1 ELSE 0 END)+(CASE WHEN q5=1 THEN 1 ELSE 0 END)+(CASE WHEN q6=1 THEN 1 ELSE 0 END)+(CASE WHEN q7=1 THEN 1 ELSE 0 END)+(CASE WHEN q8=1 THEN 1 ELSE 0 END)+(CASE WHEN q9=1 THEN 1 ELSE 0 END)+(CASE WHEN q10=1 THEN 1 ELSE 0 END)+(CASE WHEN q11=1 THEN 1 ELSE 0 END)+(CASE WHEN q12=1 THEN 1 ELSE 0 END)+(CASE WHEN q13=1 THEN 1 ELSE 0 END)+(CASE WHEN q14=1 THEN 1 ELSE 0 END)+(CASE WHEN q15=1 THEN 1 ELSE 0 END)+(CASE WHEN q16=1 THEN 1 ELSE 0 END)+(CASE WHEN q17=1 THEN 1 ELSE 0 END)+(CASE WHEN q18=1 THEN 1 ELSE 0 END)+(CASE WHEN q19=1 THEN 1 ELSE 0 END)+(CASE WHEN q20=1 THEN 1 ELSE 0 END) AS [color:#FF0000]CountOfOnes,SUM[/color]((CASE WHEN q1=2 THEN 1 ELSE 0 END)+(CASE WHEN q2=2 THEN 1 ELSE 0 END)+(CASE WHEN q3=2 THEN 1 ELSE 0 END)+(CASE WHEN q4=2 THEN 1 ELSE 0 END)+(CASE WHEN q5=2 THEN 1 ELSE 0 END)+(CASE WHEN q6=2 THEN 1 ELSE 0 END)+(CASE WHEN q7=2 THEN 1 ELSE 0 END)+(CASE WHEN q8=2 THEN 1 ELSE 0 END)+(CASE WHEN q9=2 THEN 1 ELSE 0 END)+(CASE WHEN q10=2 THEN 1 ELSE 0 END)+(CASE WHEN q11=2 THEN 1 ELSE 0 END)+(CASE WHEN q12=2 THEN 1 ELSE 0 END)+(CASE WHEN q13=2 THEN 1 ELSE 0 END)+(CASE WHEN q14=2 THEN 1 ELSE 0 END)+(CASE WHEN q15=2 THEN 1 ELSE 0 END)+(CASE WHEN q16=2 THEN 1 ELSE 0 END)+(CASE WHEN q17=2 THEN 1 ELSE 0 END)+(CASE WHEN q18=2 THEN 1 ELSE 0 END)+(CASE WHEN q19=2 THEN 1 ELSE 0 END)+(CASE WHEN q20=2 THEN 1 ELSE 0 END) AS CountOfTwos FROM ITCC_Test WHERE iAsmtID="&iAsmtIDresponse.Write(str)rs.open str, connif rs.eof = true then ' response.Write("<h2>No count done</h3>") response.End()else'Declare variables CountOfOnes = rs("CountOfOnes") CountOfTwos = rs("CountOfTwos")end ifrs.closeset rs = nothingconn.close'set conn = nothing
View 2 Replies
View Related
Mar 15, 2004
Hi everyone,
another problem:
I'm trying to count the number of rows but it's not working. Here's my code:
SELECT 'TOTAL number of rows', count(*) --This counts 4! The total number of rows in [Activites]
FROM [Activities]
WHERE [Person ID] IN
(
SELECT DISTINCT [Person ID] --This brings back 2 rows (two specific people)
FROM [Activites]
)
As my comments say, I'm wanting to count the two rows but it's counting every row. Obviously I'm doing something wrong but I can't work it out.
Any help?
Andrew
View 3 Replies
View Related
Jun 22, 2004
Hi,
I'm trying to include the COUNT(*) value of a sub-query in the results of a parent query. My SQL code is:
SELECT appt.ref, (Case When noteCount > 0 Then 1 Else 0 End) AS notes FROM touchAppointments appt, (SELECT COUNT(*) as noteCount FROM touchNotes WHERE appointment=touchAppointments.ref) note WHERE appt.practitioner=1
This comes up with an error basically saying that 'touchAppointments' isn't valid in the subquery. How can I get this statement to work and return the number of notes that relate to the relevant appointment?
Cheers.
View 6 Replies
View Related
Oct 19, 2005
Is there a way to get a SQL Stored Procedure to count the number of rows returned and then store that total in a variable?
View 1 Replies
View Related
Aug 17, 2006
Hi,
How can I aggregate a top 5 count across two satellite tables?
e.g. Orders and downloads table each have multiple entries for the same customer ID I would like to count the orders and add them to the downloads count too e.g. 5 orders added to 10 downloads giving 15 as the total for this customer and get a total 'site activity' result which I would like to select the top 5 for.
Any help or pointers would be a great help!
Thanks.
View 3 Replies
View Related
Aug 29, 2006
hi all
quick question
is there any way to set up a column that has the row count in it? i need this for a program i am developing and this would make it much easier to deal with. I know i can get a total count but when i run a count within a select statement i just get '1' for every row. thanks
tibor
View 1 Replies
View Related
Nov 29, 2006
Hi. I have a small problem that i just can't seem to figure out. I'm trying to generate a report for a case management system. The problem I am having is trying to exclude some of these results. Here is my query:
SELECT COUNT(DefendantCase.ProsAtty) AS CountOfProsAtty
FROM DefendantCase LEFT JOIN DefendantEventPros ON DefendantCase.VBKey=DefendantEventPros.VBKey
WHERE DefendantCase.StatusID=17 AND DefendantCase.ProsAtty=55
AND DefendantEventPros.EventDate BETWEEN DATEADD(DAY,-60,GETDATE()) AND GETDATE() AND DefendantEventPros.EventID=9
This query is trying to find the total amount of cases where the statusid=17, the prosatty=55, the date is between today and 60 days ago, and there is an eventid=9.
now, i'm not getting errors in the query itself; it's just that it's inflating the total number. If a case has more than one eventid=9, it will include that extra in the results. I do not want to include those in the results. Does anyone have any suggestions? Thanks!
View 3 Replies
View Related
Mar 10, 2004
Hi,
I have a temp table that I use to calculate prices from and I need to know how many of each row with the same serialnumber.
I figured I could add a column that contains that number. But how will I get it there?
UPDATE [_temp] T1
SET T1.SERIAL_COUNT = (SELECT COUNT(*) FROM [_temp] T2 WHERE T1.SERIAL = T2.SERIAL)
Doesn't work. Any ideas?
View 5 Replies
View Related
Mar 27, 2004
hiya all,
I got a table that is represented as a B-Tree, it has a one to
many relation with its self
------------
| UserID |
------------
|FatherID|
------------
UserID = FatherID
it looks like this
o
/// \
ooo ooo
//\ ///\
oo ooo ooo
I want to get all the nodes that has 256 children and grand children and each node is limited to have 6 nodes under it.
any ideas ? the DB isn't filled with data yet so we can add more fields
to the table if it helps.
I could write a trigger that increments a counter in each node but it will be a recursive trigger so it can go from the child to its father and the father to its father upating them and so on.
regards
Ahm
View 7 Replies
View Related
Apr 23, 2008
I have a table that I want to use for reports that holds these datatypes
safe: Integer
unsafe: Integer
made_safe: Bit
unobserved: Bit
Which gets populated.
I want to count the values in each field but Bit fields seem to be awkward. This is my attempt
SELECT
SUM(safe)as TotalSafe,
SUM(unsafe)as TotalUnsafe,
COUNT(Case WHEN unobserved = 1 THEN 1 ELSE NULL END)as NotSeen,
COUNT(Case when made_safe = 1 THEN 1 ELSE NULL END) as TotalMadeSafe
FROM myTable
Any ideas please
View 3 Replies
View Related
Jun 4, 2008
Hi all,
I am trying to count the total amount of times the primary key is used within each month of the year "JAN", "FEB" "MAR"... Unfortunately I have no idea how to A) Search by month and B) Count the total amount of times data within the new table that is created AFTER the outer joins are done. I hope you can help.
Thanks.
SELECT
*
FROM
tableA ta,
tableB tb,
tableC tc
WHERE tc.updated_date >= TO_DATE ('01/05/2008 00:00:00','DD/MM/YYYY HH24:MI:SS')
AND tc.updated_date <= TO_DATE ('31/05/2008 00:00:00','DD/MM/YYYY HH24:MI:SS')
AND tc.primaryKey = tb.primaryKey (+)
AND tc.primaryKey = ta.primaryKey (+)
AND count(tc.priarykey) -- This is what I want to count
;
View 2 Replies
View Related
Oct 11, 2013
T-SQL counting rows with a value:
My query is like this:
SELECT
Name,
A,
B,
C,
D
FROM
Table
ORDER BY
Name
The query result looks like this:
Name A B C D
Bert 2 0 0 0
Bert 3 6 0 1
Mark 0 0 1 0
Mark 0 8 8 0
Mark 5 6 3 0
I want my result to count the cells with a value and group them on name Like this:
Name A B C D
Bert 2 1 0 1
Mark 3 2 3 0
COUNT() gives back every row from a name.
SUM() adds up the values
I have tried many things with sub queries and combined functions, but up until now with no results.
How do I do this ?
View 4 Replies
View Related
Mar 21, 2015
select statement joining file1 to file2. File 1 may have 0, 1, or many corresponding rows in file2. I need to count the corresponding rows in table2. Table2 also has a Boolean column and I need to count the number of rows where it is true. So I need to count the total number of matching rows and the count of those that are set to true. This is an example of what I have so far. I had to add each column being selected into a Group by to make it work, but I do not know why. Is there some other way this should be set up.
SELECT c.CarId, c.CarName, c.CarColor, COUNT(t.TrailerId) as trailerCount, (add count of boolian, say t.TrailerFull is true)
FROM Car c
LEFT JOIN Trailer t on t.CarId = c.CarId
GROUP BY c.CarId, c.CarName, c.CarColor
View 4 Replies
View Related
Jun 10, 2006
Hi All
I have:
select prod_id , count(prod_id)[Number of Times]
from orders_tbl
group by prod_id
order by 1;
I get a result however, how do sum up the total number of times ?
Thanks
View 5 Replies
View Related
Aug 18, 2006
Hello all.
I am looking to select all from a particular row where one field occurs more than once. The code i'm, trying isnt working:
select * from U_DATAFILE
where count(group by VIEW_ACTION)>1
Thanks all :)
View 10 Replies
View Related
Feb 15, 2007
is there a way to count the sort of a query and still get the values in the query
columns1, column2, column3 are the same for 5 rows so give the values of each column and then count =5
View 8 Replies
View Related
Apr 5, 2007
Hi I am fairly new to this, and I searched the forums before posting this, but I couldn't find anything that applies. I was having trouble removing/counting for records that include a certain value.
Ex, I want to know how many records in the LASTNAME field have an @ anywhere in the field. I formerly used FoxPro and the way I could check was "Count for atc("@",LASTNAME)<>0".
I tried this sql query " SELECT COUNT(*) AS COUNT FROM `DATABASE` WHERE LOCATE(‘@’, LASTNAME)>0 " however, it didn't work. Can someone please help?
View 3 Replies
View Related
Apr 13, 2007
All,
I have a table that was created for a web form. The fields are populated with Y, N, NC or NA.
I need to total these for each record. There are about 40 fields
in each record.
The expected result
Form Y N NA
1 4 2 34
2 8 2 30
3 40 0 0
Thanks for you Help
View 6 Replies
View Related
May 3, 2007
Hi, I'm attempting to write a small query that will search all entries in a colum and compair their values to see if there are any duplicates, then display only the duplicate entries. I'm running into trouble when using ms sql 2000 in that it does not like the COUNT statement. Any ideas?
View 4 Replies
View Related
May 29, 2007
I have a table with three fileds
1 - SmsTime (datatype = datetime)
2 - MsgCnt (datatype = int)
3 - Message (datatype = varchar)
Here MsgCnt means it will give the number of sms part (70 charector is equal to One SMS).
So in each row MsgCnt may be vary. Some time it may be 1 or 3 or 5 or 2 etc.
Now here I need to count how many MsgCnt is received on Today. Its not to count the rows but counting the MsgCnt fild value.
Is there any sql way to find it.
Plesase help me!
View 8 Replies
View Related
Jun 8, 2007
I need to add up the number of people who joined this month and compare that number to the number of people who joined last month and display the results in a report:
FirstName..LastName.....StartedDate
Randy......Simpson......5/4/2007 10:00:00 PM
Steve......Rowe.........5/2/2007 10:00:00 PM
Eric.......Dickerson....5/4/2007 10:00:00 PM
Gloria.....Sanches......5/1/2007 12:00:29 AM
Andres.....Marcelino....5/1/2007 12:06:31 AM
katie......ryan.........6/4/2007 12:08:35 AM
Denise.....River........6/4/2007 12:27:14 AM
Kellog.....Stover.......6/5/2007 12:37:20 AM
Glenn......Sanders......6/1/2007 12:42:40 AM
View 15 Replies
View Related
Feb 26, 2008
Is there a way to count days between two dates according to a specific calendar (for example, Italian banking holidays calendar)?
If there isn't, how could I count days between two dates without counting Saturdays and Sundays? In Excel there's the command DAY.WEEK with which week days are numbered from 1 (Monday) to 7 (Sunday) and I can instruct: "if weekday is <= 4 then weekday + 1 else weekday + 3. Of course Excel has a calendar beneath and knows that, say, 20-02-2008 is Wednesday (day 3 of the week).
Thank you
Anna - Verona (Italy)
View 4 Replies
View Related
Jul 20, 2005
I'm having trouble getting my head around this, and no one in the groupshas posted exactly the problem.The table below tracks site traffic across a network. There is 1 rowper pageview and UUID is that user's unique cookie.CREATE TABLE [dbo].[Stats_Working] ([inac_stats_id] [int] NOT NULL ,[hit_time] [datetime] NULL ,[site_id] [int] NULL ,[site_cat_id] [int] NULL ,[item_id] [int] NULL ,[local_content_cat_id] [int] NULL ,[UUID] [float] NULL ,[USER_AGENT] [char] (50) NULL) ON [PRIMARY]GOA client asked: of these pageviews and within each category, how manyare accounted for by users that generated 2 or fewer pageviews, 3+pageviews, and 4+ pageviews?I said, "yes, we have that information". I know it's here, but I'mbraindead from looking at what should be a simple solution.Thanks for any helpCam Bevis*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 5 Replies
View Related
Mar 26, 2007
Hi, I am trying to restore a database through Visual Basic .NET 2005 which I managed to backup successfully.
However when I try to restore the database even though I have created a server to use as the admin side to perform the restore and closed the connection to the target database I still get an error saying database in use cannot get exclusive hold?
Could someone tell me how I count or view open connections please?
Thanks
View 18 Replies
View Related
Jul 19, 2007
I have a column in a report with values Y or N or V. In the header, I have to show the number of times each value appeared.
I used RepeatingValue() with IIF() but it's counting all the values as same, e.g., if there are four occurrences of Y, two of N and one of V, I want something like this:
Y Cnt=4, N Cnt=2, V Cnt=1
My assumption was that this will work (white spaces added for readability:
=iif(
Fields!myCol.Value = "Y",
"Y Cnt=" & RunningValue(Fields!myCol.Value, Count, "dsMyDataSet"),
iif(Fields!myCol.Value = "N",
"N Cnt= & RunningValue(Fields!myCol.Value, Count, "dsMyDataSet"),
iif(Fields!myCol.Value = "V",
"V Cnt= & RunningValue(Fields!myCol.Value, Count, "dsMyDataSet"),
"NULL"
)
)
)
Please help.
View 3 Replies
View Related
Dec 5, 2007
Why does Code Block 1 return the correct count information instead of Code Block 2? Code Block 1 returns the correct value, but Code Block 2 return values that I don't understand. Can anyone explain why Code Block 1 works and could it be better?
Code Block 1
select
DistrictID,
DistricName,
SchoolID,
SchoolName,
TeacherID,
TeacherName,
StudentID,
StudentName,
[Number of Students for a given Teacher],
[Number of Students for a given School],
[Number of Students for a given District],
max([Rank of Teachers for a given School]) over (partition by SchoolID) [Number of Teachers for a given School],
max([Rank of Teachers for a given District]) over (partition by DistrictID) [Number of Teachers for a given District],
max([Rank of Schools for a given District]) over (partition by DistrictID) [Number of Schools for a given District],
max([Rank of Districts]) over (partition by 1) [Number of Districts]
from
(
select
DistrictID,
DistricName,
SchoolID,
SchoolName,
TeacherID,
TeacherName,
StudentID,
StudentName,
Count(StudentID) over (Partition By TeacherID) [Number of Students for a given Teacher],
Count(StudentID) over (Partition By SchoolID) [Number of Students for a given School],
Count(StudentID) over (Partition By DistrictID) [Number of Students for a given District],
dense_rank () over (Partition By SchoolID order by TeacherID) [Rank of Teachers for a given School],
dense_rank () over (Partition By DistrictID order by TeacherID) [Rank of Teachers for a given District],
dense_rank () over (Partition By DistrictID order by SchoolID) [Rank of Schools for a given District],
dense_rank () over (Partition By 1 order by DistrictID) [Rank of Districts]
from
yourtable/query
) as data
VS.
Code Block 2
select
DistrictID,
DistricName,
SchoolID,
SchoolName,
TeacherID,
TeacherName,
StudentID,
StudentName,
Count(StudentID) over (Partition By TeacherID) [Number of Students for a given Teacher],
Count(StudentID) over (Partition By SchoolID) [Number of Students for a given School],
Count(StudentID) over (Partition By DistrictID) [Number of Students for a given District],
Count(TeacherID) over (Partition By SchoolID ) [Number of Teachers for a given School],
Count(TeacherID) over (Partition By DistrictID ) [Number of Teachers for a given District],
Count(SchoolID) over (Partition By DistrictID ) [Number of Schools for a given District],
Count(DistrictID) over (Partition By 1 ) [Number of Districts]
from
yourtable/query
View 3 Replies
View Related
Dec 15, 2007
Hi Guy's,
New to servers,
I'm trying to change my Access query into a Server View but I am having trouble counting my Yes/No field that has become a bit field [RevReq].
This is what I have tried but [RevReq] gives me the same count as the [PayNumber] field which is counting the records in the table:
SELECT TOP (100) PERCENT StaffListSQL.dbo.StaffList.Level1, StaffListSQL.dbo.StaffList.[Group], StaffListSQL.dbo.StaffList.Discipline AS Disc,
COUNT(dbo.tblReviews.PayNumber) AS Staff, COUNT(dbo.tblReviews.RevReq) AS Rev, COUNT(dbo.tblReviews.AppStatus) AS RevApp
FROM dbo.tblReviews RIGHT OUTER JOIN
StaffListSQL.dbo.StaffList ON dbo.tblReviews.PayNumber = StaffListSQL.dbo.StaffList.[Pay Number]
GROUP BY StaffListSQL.dbo.StaffList.[Group], StaffListSQL.dbo.StaffList.Discipline, StaffListSQL.dbo.StaffList.Level1
ORDER BY StaffListSQL.dbo.StaffList.Level1
Here is what is returned:
Acute Clyde Acute Services PTB 16 16 0
Acute Clyde Acute Services S&P 0 0 0
Acute Diagnostic Services A&C 16 16 0
Acute Diagnostic Services AFC 15 15 0
Acute Diagnostic Services AHP 482 482 0
Acute Diagnostic Services ASC 338 338 0
Acute Diagnostic Services MAINT 1 1 0
Acute Diagnostic Services MED 0 0 0
Acute Diagnostic Services MISC 0 0 0
Acute Diagnostic Services NURSE - Q 765 765 0
Acute Diagnostic Services NURSE - U 62 62 0
Acute Diagnostic Services PTB 85 85 0
Acute Diagnostic Services S&P 10 10 0
Acute Emergency Care & Medical Specialties NULL 1 1 0
Acute Emergency Care & Medical Specialties A&C 48 48 0
Hope this makes sense and thanks in advance.
View 5 Replies
View Related
Nov 9, 2006
I'm hoping that someone has the answer. I guess I may be missing the obvious but I'm struggling to do this.
Simplified, I have two tables:- One table contains a list of Customers and the other table a list of Products that they have purchased. They are linked by a customer reference no, a one-to-many relationship.
What I want to be able to do is count have how many Customers have purchased both Product A and Product B.
Can anyone tell me the SQL statement that would return the answer as a single number.
Thanks in expectation
View 5 Replies
View Related
