Solved

Follow Up TO Grouping/Output Question

Posted on 2006-06-14
11
174 Views
Last Modified: 2013-12-24
I had previously asked a question about getting a certain type of output and display from a query - relating to Count and Grouping. I thought I could then perform a join - but I overestimated my underestanding.

The method I used for getting the query to work were sub selects (thanks to those who responded!).

Here is some data from the table.
The headings are:
LATRecordID     SessionRecordID     Skill     PreCheckRating     Type     PostCheckRating

LATRecordID is just the entry or row number
SessionRecordID ties one persons record together across several tables - and will be slected by a form input - that I can get to work just fine.

The data is:

6730     247     E127     3     0     3
6731     247     E131     2     0     3
6732     247     G001     3     0     4
6733     247     G012     2     1     3
6734     247     G015     3     2     3
6748     248     E127     3     2     4
6749     248     E131     3     2     3
6750     248     G001     3     2     3
6751     248     G012     2     1     4
6752     248     G015     2     1     3
6753     248     G016     1     1     4
6764     248     K011     3     2     3
6765     249     C310     1     1     2
6779     249     K008     3     2     4
6780     249     K009     2     1     4
6781     249     K010     3     2     4
6782     249     K011     3     2     4
6783     251     C310     2     1     3
6784     251     E127     3     2     3
6785     251     E131     2     1     3
6786     251     G001     3     2     3
6787     251     G012     3     2     3
6788     251     G015     3     2     3
6789     251     G016     3     2     3
6790     251     G019     3     2     3

The query is basically:
select skill,
     (select count(PreCheckRating) from EE_TestTable where PreCheckRating = 1 and skill = E1.skill) as [preCheck-1],
     (select count(PreCheckRating) from EE_TestTable where PreCheckRating = 2 and skill = E1.skill) as [preCheck-2],
     (select count(PreCheckRating) from EE_TestTable where PreCheckRating = 3 and skill = E1.skill) as [preCheck-3],
     (select count(Type) from EE_TestTable where Type = 1 and skill = E1.skill) as [Type-1],
     (select count(Type) from EE_TestTable where Type = 2 and skill = E1.skill) as [Type-2],
     (select count(PostCheckRating) from EE_TestTable where PostCheckRating = 1 and skill = E1.skill) as [PostCheck-1],
     (select count(PostCheckRating) from EE_TestTable where PostCheckRating = 2 and skill = E1.skill) as [PostCheck-2],
     (select count(PostCheckRating) from EE_TestTable where PostCheckRating = 3 and skill = E1.skill) as [PostCheck-3]
from TableName E1
group by skill
order by skill

In this same table I have an identifying field called SessionRecordID, which is used across the database to tie many bits of information together. THis ID in turn is tied to a session and that is the filter I want to use for my data.

What I have done on other templates is to join the query pulling the data to the "filter" buy using the following:

INNER JOIN
SessionRecord ON
sessionRecord.sessionRecordID = TABLENAME.SessionRecordID)
WHERE
sessionID = #Form.SessionID#


When I do this for this particular template I do get the correct records pulled out, but the counts are for all entries. IOW - it selects the set of skill correctly, and it counts correctly - but it counts for every skill in the table regardless of session, not just for the session/sessionRecord I need.

The origional question is at:
http://www.experts-exchange.com/Web/WebDevSoftware/ColdFusion/Q_21878303.html

Thank you,
tim
0
Comment
Question by:timponder
  • 6
  • 5
11 Comments
 
LVL 2

Expert Comment

by:pharmacychoice
ID: 16906911
Tim --

If I am understanding you right, you are performing this inner join on the main from statement in the query.  If this is correct, what if you change your inner hoin to this:

INNER JOIN
SessionRecord ON
sessionRecord.sessionRecordID = E1.SessionRecordID)
WHERE
sessionID = #Form.SessionID#
0
 

Author Comment

by:timponder
ID: 16907045
Well that is what I thought - but I am doing something wrong somewhere else then - as it does not work. Regardless of the form entry - I always get the right skills but the total counts for all sessions.
 This is what it looks like as a whole (using a set value for testing instead of the form variable):

select skill,
     (select count(PreCheckRating) from EE_TestTable where PreCheckRating = 1 and skill = E1.skill) as [preCheck-1],
     (select count(PreCheckRating) from EE_TestTable where PreCheckRating = 2 and skill = E1.skill) as [preCheck-2],
     (select count(PreCheckRating) from EE_TestTable where PreCheckRating = 3 and skill = E1.skill) as [preCheck-3],
     (select count(Type) from EE_TestTable where Type = 1 and skill = E1.skill) as [Type-1],
     (select count(Type) from EE_TestTable where Type = 2 and skill = E1.skill) as [Type-2],
     (select count(PostCheckRating) from EE_TestTable where PostCheckRating = 1 and skill = E1.skill) as [PostCheck-1],
     (select count(PostCheckRating) from EE_TestTable where PostCheckRating = 2 and skill = E1.skill) as [PostCheck-2],
     (select count(PostCheckRating) from EE_TestTable where PostCheckRating = 3 and skill = E1.skill) as [PostCheck-3]
from

(TableName E1
INNER JOIN
SessionRecord ON
sessionRecord.sessionRecordID = E1.SessionRecordID)
WHERE
sessionID = 55

group by skill
order by skill


tim
0
 
LVL 2

Accepted Solution

by:
pharmacychoice earned 250 total points
ID: 16907082
You are getting the total counts because the sub-selects are not restricted to the session id.  They are pulling totals.  You may need to try to add the inner join into the sub-select as well to limit that number.  The way that it stands now, you are limiting the skills to the particular ones for the session, but since the sub-selects are not changing, all totals are still pulled.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:timponder
ID: 16907202
I thought of that right after I sent the message.
I will give that a shot, I am sure you are right about the problem - I am just getting more and more doubtful about my ability.
:)
tim
0
 
LVL 2

Expert Comment

by:pharmacychoice
ID: 16907226
Well, you also have to consider that the way that this data is being pulled is a bit of a hack due to the structure of the data.  In a perfect world, there would be a relational table that associates your skills to the rest of the data and then this would be a bit easier to pull.  Do not start doubting your skills because my hack is not the easiest to follow.  ;-)
0
 

Author Comment

by:timponder
ID: 16907569
A hack I appreciate - since the database is set, at least for now.

I will see what I can do here - just seems like each time I think oh I can do that, I can then pretty much guarentee I will have a problem. :)

tim
0
 
LVL 2

Expert Comment

by:pharmacychoice
ID: 16907618
Let me know if this solution works.
0
 
LVL 2

Expert Comment

by:pharmacychoice
ID: 16934918
Tim,

Did this solution work for you?
0
 

Author Comment

by:timponder
ID: 16935033
Hi,
I am back on this piece now and _think_ I have it working.

Here is what I have with an explination below. Othere than the fact the database does not let me (us) do this in an ideal way, can you see anything that looks like it will present problems or is incorrect. The sample data seems to be returning the correct values. I only included two selects for precheck and type to save space:
====
select skill,
(select count(PreCheckRating)
 from (EE_TestTable
Inner Join SessionRecord ON sessionrecord.sessionrecordID = EE_TestTable.SessionRecordID)
where
SessionID = 55 AND PreCheckRating = 2 and skill = E1.skill) as [preCheck-2],

(select count(PreCheckRating)from (EE_TestTable
Inner Join SessionRecord ON sessionrecord.sessionrecordID = EE_TestTable.SessionRecordID)
where SessionID = 55  AND PreCheckRating = 3 and skill = E1.skill) as [preCheck-3]
from
(TableName E1
INNER JOIN
SessionRecord ON
sessionRecord.sessionRecordID = E1.SessionRecordID)
WHERE
sessionID = 55

group by skill
order by skill
==

That all seemed to work and pull the right data. I had to leave the second join in (outside of the counts) to get it to limit the skills to that session as well. Wasnt able to figur out any "short cut" for that.

What do you think?
tim
0
 
LVL 2

Expert Comment

by:pharmacychoice
ID: 16935301
Tim,

This looks like it should work to me.  I think that the only thing that you were missing previously was the join for your sub-selects.
0
 

Author Comment

by:timponder
ID: 16935331
Thank you again for all of your help - by talking through it I _almost_ understand how and why it works. :)

tim
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question