Combining three queries [Resolved]

[olamm2k]

Ref:
1. http://www.vbforums.com/showthread.php?t=328403 for table descriptions
2. http://www.vbforums.com/showthread.php?t=328505 for some of the query stuff I was doing last night
3. http://www.vbforums.com/showthread.php?t=240883 for details of someone who wanted something similar

I'm trying to combine three queries to produce a report for each member, passing the parameter intMemID into a query to return:
1. The member's name
2. The number of sessions they have been to
3. The total number of sessions that have occurred

Using the following, I can get all of the information:
(SELECT Forenames & Chr(32) & Surname FROM Members WHERE MemID = intMemID)
UNION ALL
(SELECT Count(MemID) AS SessionsAttended FROM RegMem WHERE MemID = intMemID)
UNION ALL
(SELECT COUNT (SDate) FROM (SELECT SDate FROM RegMem GROUP BY SDate))

But my question is, how do I modify the query so that instead of having the data displayed in 3 separate rows, it is displayed as 3 fields in one row?

I've been messing around with INNER JOIN for about an hour now to get what I want, and it's just not coming.

[olamm2k]

Still not got anywhere with this...

[szlamany]

In order to really help, I would need to see a but bit more of what the data really looks like. I can whip you up a query very easily, but in order to not do it 6 times, I need to see real data.

Something like:

Member

MemberId   ForeName   SurName
0000001    Tom        Smith

and the other tables also - they are more important...

[olamm2k]

If you have a look at the first link I gave, I explained my table design there.

There's one table like you've shown, and another (RegMem) with:
SDate - MemID
01/02/2005 - 8
01/02/2005 - 9
01/02/2005 - 10
01/02/2005 - 15
08/02/2005 - 8
08/02/2005 - 10

[szlamany]

I did this in QUERY ANALYZER...

begin tran
create table member (intmemberid int,forename varchar(10), surname varchar(10))
insert into member values (8,'TOM','SMITH')
insert into member values (9,'JOHN','JONES')
insert into member values (10,'JACK','ADAMS')
insert into member values (15,'ANN','BLACK')
create table regmem (sdate datetime,memid int)
insert into regmem values ('2005-01-02',8)
insert into regmem values ('2005-01-02',9)
insert into regmem values ('2005-01-02',10)
insert into regmem values ('2005-01-02',15)
insert into regmem values ('2005-08-02',8)
insert into regmem values ('2005-08-02',10)
insert into regmem values ('2005-08-02',10)
select * from member
select * from regmem
select intmemberid,forename,surname
 ,sum(1) "Visits for member"
 ,count(distinct sdate) "Distinct dates"
 from regmem 
 left join member on intmemberid=memid
  group by intmemberid,forename,surname
rollback

and got these results

intmemberid forename   surname    
----------- ---------- ---------- 
8           TOM        SMITH
9           JOHN       JONES
10          JACK       ADAMS
15          ANN        BLACK

sdate                                                  memid       
------------------------------------------------------ ----------- 
2005-01-02 00:00:00.000                                8
2005-01-02 00:00:00.000                                9
2005-01-02 00:00:00.000                                10
2005-01-02 00:00:00.000                                15
2005-08-02 00:00:00.000                                8
2005-08-02 00:00:00.000                                10
2005-08-02 00:00:00.000                                10

intmemberid forename   surname    Visits for member Distinct dates 
----------- ---------- ---------- ----------------- -------------- 
8           TOM        SMITH      2                 2
9           JOHN       JONES      1                 1
10          JACK       ADAMS      3                 2
15          ANN        BLACK      1                 1

I do not understand what you really want for the two last columns. It would appear to me from your example that you would get the same values in both columns - number of visits for the member. What do you really want in the last column. I made it show "distinct" visit dates - member #10 had only two distinct dates - in 3 visits...

[olamm2k]

Yes, I think you misunderstood. The last column will always be the same - it is simply a count of the number of distinct dates in the table, not distinct dates that the member has visited on, as that will be the same as the number of sessions they have been to.

(SELECT COUNT (SDate) FROM (SELECT SDate FROM RegMem GROUP BY SDate)) returns this count.

I'm not trying to create a table, though - it's discrete data I'm extracting about each member, so that I can create a report, eg:
MemID - 8
Surname - Smith
Forenames - Tom
Sessions Attended - 2 out of 5

[szlamany]

Yes, I think you misunderstood. The last column will always be the same - it is simply a count of the number of distinct dates in the table, not distinct dates that the member has visited on, as that will be the same as the number of sessions they have been to.

(SELECT COUNT (SDate) FROM (SELECT SDate FROM RegMem GROUP BY SDate)) returns this count.

I'm not trying to create a table, though - it's discrete data I'm extracting about each member, so that I can create a report, eg:
MemID - 8
Surname - Smith
Forenames - Tom
Sessions Attended - 2 out of 5

I wasn't trying to show you how to create a table - just needed to do that myself so I could have some test data in order to help you with your problem.

Now this query does what you want (the query in bold that is):

begin tran
set nocount on
create table member (intmemberid int,forename varchar(10), surname varchar(10))
insert into member values (8,'TOM','SMITH')
insert into member values (9,'JOHN','JONES')
insert into member values (10,'JACK','ADAMS')
insert into member values (15,'ANN','BLACK')
create table regmem (sdate datetime,memid int)
insert into regmem values ('2005-01-02',8)
insert into regmem values ('2005-01-02',9)
insert into regmem values ('2005-01-02',10)
insert into regmem values ('2005-01-02',15)
insert into regmem values ('2005-08-02',8)
insert into regmem values ('2005-08-02',10)
insert into regmem values ('2005-08-02',10)
select * from member
select * from regmem
select intmemberid,forename,surname
 ,sum(1) "Visits for member"
 ,(select count(distinct sdate) from regmem) "Distinct dates in table"
 from regmem 
 left join member on intmemberid=memid
  group by intmemberid,forename,surname
rollback

Giving these results:

intmemberid forename   surname    
----------- ---------- ---------- 
8           TOM        SMITH
9           JOHN       JONES
10          JACK       ADAMS
15          ANN        BLACK

sdate                                                  memid       
------------------------------------------------------ ----------- 
2005-01-02 00:00:00.000                                8
2005-01-02 00:00:00.000                                9
2005-01-02 00:00:00.000                                10
2005-01-02 00:00:00.000                                15
2005-08-02 00:00:00.000                                8
2005-08-02 00:00:00.000                                10
2005-08-02 00:00:00.000                                10

intmemberid forename   surname    Visits for member Distinct dates in table 
----------- ---------- ---------- ----------------- ----------------------- 
8           TOM        SMITH      2                 2
9           JOHN       JONES      1                 2
10          JACK       ADAMS      3                 2
15          ANN        BLACK      1                 2

[olamm2k]

That doesn't work. I'm using Access, so I can't use COUNT(DISTINCT SDate) for one thing. It also doesn't like sum(1) "Visits for member" - it has to be sum(1) AS "Visits for member". Even after changing that, I still get 'Join Expression not supported'.

[szlamany]

That doesn't work. I'm using Access, so I can't use COUNT(DISTINCT SDate) for one thing. It also doesn't like sum(1) "Visits for member" - it has to be sum(1) AS "Visits for member". Even after changing that, I still get 'Join Expression not supported'.

I don't do ACCESS - but try this:

select intmemberid,forename,surname
 ,count(*) as SessionsAttendance
 from regmem 
 left join member on intmemberid=memid
  group by intmemberid,forename,surname

If it doesn't work, copy/paste your query so I can make sure you don't have some other syntax error.

Not sure what you can do about the lack of COUNT(DISTINCT...) in ACCESS - sorry.

[szlamany]

This search had some stuff that might help - others have asked this question before...

Search for ACCESS COUNT DISTINCT

[olamm2k]

I have no problems with the count distinct - I'm using (SELECT COUNT (SDate) FROM (SELECT SDate FROM RegMem GROUP BY SDate)), as I explained before.

I'm still getting a 'join expression not supported' error. This seems to be because the ON clause includes a variable.

[szlamany]

I have no problems with the count distinct - I'm using (SELECT COUNT (SDate) FROM (SELECT SDate FROM RegMem GROUP BY SDate)), as I explained before.

I'm still getting a 'join expression not supported' error. This seems to be because the ON clause includes a variable.

I was wondering why intMemberId looked like a variable...

Replace it with the actual column name.

SQL Server doesn't allow you to embed VB variables in query lines like that.

[olamm2k]

It works like this:
select regmem.memid, forenames, surname, count(*) as SessionsAttendance
from (select memid from regmem where memid=intmemid) regmem
left join members
on regmem.memid = members.memid
group by regmem.memid, forenames, surname;

But I still also need to include the count distinct part (SELECT COUNT (SDate) FROM (SELECT SDate FROM RegMem GROUP BY SDate)) as the last field.

[olamm2k]

Looks like this should work:
PARAMETERS [intMemID] Integer;
SELECT TempTable.MemID, Forenames, Surname, COUNT(TempTable.MemID) AS SessionsAttendance, (SELECT COUNT (SDate) FROM (SELECT SDate FROM RegMem GROUP BY SDate)) AS TotalDates
FROM (SELECT MemID FROM RegMem WHERE MemID=[intMemID]) AS TempTable
INNER JOIN Members
ON TempTable.MemID = Members.MemID
GROUP BY TempTable.MemID, Forenames, Surname;

[szlamany]

Looks like this should work:
PARAMETERS [intMemID] Integer;
SELECT TempTable.MemID, Forenames, Surname, COUNT(TempTable.MemID) AS SessionsAttendance, (SELECT COUNT (SDate) FROM (SELECT SDate FROM RegMem GROUP BY SDate)) AS TotalDates
FROM (SELECT MemID FROM RegMem WHERE MemID=[intMemID]) AS TempTable
INNER JOIN Members
ON TempTable.MemID = Members.MemID
GROUP BY TempTable.MemID, Forenames, Surname;

Looks good to me - hope I helped

[olamm2k]

You did, thanks. I didn't realise you could put subqueries as a field like that before.

[olamm2k]

Just realised - using my original query, I simply needed to rewrite it to:
SELECT Forenames & Chr(32) & Surname AS FullName,
(SELECT Count(MemID) FROM RegMem WHERE MemID = intMemID) AS SessionsAttended,
(SELECT COUNT (SDate) FROM (SELECT SDate FROM RegMem GROUP BY SDate)) AS TotalDates
FROM Members WHERE MemID = intMemID

From:
(SELECT Forenames & Chr(32) & Surname AS FullName FROM Members WHERE MemID = intMemID)
UNION ALL
(SELECT Count(MemID) AS SessionsAttended FROM RegMem WHERE MemID = intMemID)
UNION ALL
(SELECT COUNT (SDate) AS TotalDates FROM (SELECT SDate FROM RegMem GROUP BY SDate))

[szlamany]

Just realised - using my original query, I simply needed to rewrite it to:
SELECT Forenames & Chr(32) & Surname AS FullName,
(SELECT Count(MemID) FROM RegMem WHERE MemID = intMemID) AS SessionsAttended,
(SELECT COUNT (SDate) FROM (SELECT SDate FROM RegMem GROUP BY SDate)) AS TotalDates
FROM Members WHERE MemID = intMemID

From:
(SELECT Forenames & Chr(32) & Surname AS FullName FROM Members WHERE MemID = intMemID)
UNION ALL
(SELECT Count(MemID) AS SessionsAttended FROM RegMem WHERE MemID = intMemID)
UNION ALL
(SELECT COUNT (SDate) AS TotalDates FROM (SELECT SDate FROM RegMem GROUP BY SDate))

I agree that it can be done the way you suggest, but in general your goal should be to reduce sub-queries use as much as possible.

The method I suggested reads through the REGMEM table just once - and uses the GROUP BY/AGGREGATE logic to do it's job.

In MS SQL Server - with COUNT(DISTINCT...) it would be possible to have no sub-queries at all - just one scan of data from REGMEM and the aggregate functions do there jobs.

With sub-queries, the data engine must evaluate each "sub-query" for each row presented - in most cases.