I've been hesitating to send this because:
1. You seem to be happy with the first solution I posted
and
2. This solution seems so much more complicated than the first solution that
it seems counter-intuititive that it would perform better. I think you
should test both solutions to see which performs better. The first solution
I posted requires the query engine to run 4 subqueries for every row in
your CirID table! This is a lot of processing. This alternate solution,
while it involves more intermediate queries, may perform better because only
these intermediate queries are being run.
Give it a try.
----------------------------------------------------------------------------
------
<snip>
If the above assumptions are correct, then let's start. The first step is to
normalize your Verified table. It is a very bad design to have 4 PetNCirc
columns. You should add a new table called PetCirc and insert the data as
follows:
ID Type CirID
1 A 101
1 B 100
1 C 122
1 D 112
2 A 122
2 B 123
2 C 112
2 D 123
etc.
The benefit of this design is that there is no problem adding another type.
With your original design, you would have to modify the table design to add
another column, and then modify all your queries to handle the new column.
The other benefit of this design is that a simple grouping query can be used
to get your counts:
select CirID, Type, count(*) as Total
From PetCirc
Group By CirID, Type
If you cannot change the design of the database for some reason, then you
will
need to create a view that will return the data in a normalized structure:
CREATE VIEW vPetCirc AS
SELECT ID, 'A' As Type, PetACirc As CirID FROM Verified
UNION ALL
SELECT ID, 'B', PetBCirc FROM Verified
UNION ALL
SELECT ID, 'C', PetCCirc FROM Verified
UNION ALL
SELECT ID, 'D' , PetDCirc FROM Verified
Now, after running this iew creation script, you can get the raw data for yo
ur result by joining CirID to vPetCirc:
SELECT c.Cir_ID, c.First_Name, c.First_Name, v.Type, Count(ID) AS Total
FROM CirID AS c LEFT JOIN vPetCirc AS v ON c.Cir_ID = v.CirID
GROUP BY c.Cir_ID, c.First_Name, c.First_Name, v.Type;
ORDER BY c.Cir_ID,v.Type
This yields the following results from your sample data (after I changed
John Cronin's DirID to 122):
Cir_ID First_Name Last_Name Type Total
100 Tony Cornelius B 1
100 Tony Cornelius C 1
101 Paul DeKeyser A 1
102 Mary Andrews A 1
102 Mary Andrews C 1
103 Clint Backhaus <null> 0
105 Roger Brummont D 1
106 Theresa Corbino <null> 0
107 Andrew Cornelius <null> 0
108 Paul Cramer <null> 0
122 John Cronin A 6
122 John Cronin B 5
122 John Cronin C 5
122 John Cronin D 7
Now you could create your desired html table by looping through this result
in
your vbscript code, writing the data to the appropriate columns in your html
table, and writing zeros where there is missing data.
Or you could do it with sql, like this:
First, create this view:
CREATE View vAllCirIDsAndTypes AS
SELECT Cir_ID, 'A' As Type FROM CirID
UNION ALL
Select Cir_ID, 'B' FROM CirID
UNION ALL
Select Cir_ID, 'C' FROM CirID
UNION ALL
Select Cir_ID, 'D' FROM CirID
Now, this query:
SELECT c.Cir_ID, c.First_Name, c.Last_Name, c.Type, Count(ID) AS Total
FROM vAllCirIDsAndTypes AS c LEFT JOIN vPetCirc AS v
ON c.Cir_ID = v.CirID AND c.Type = v.Type
GROUP BY c.Cir_ID, c.First_Name, c.Last_Name, c.Type
returns these results:
Cir_ID First_Name Last_Name Type Total
100 Tony Cornelius A 0
100 Tony Cornelius B 1
100 Tony Cornelius C 1
100 Tony Cornelius D 0
101 Paul DeKeyser A 1
101 Paul DeKeyser B 0
101 Paul DeKeyser C 0
101 Paul DeKeyser D 0
102 Mary Andrews A 1
102 Mary Andrews B 0
102 Mary Andrews C 1
102 Mary Andrews D 0
103 Clint Backhaus A 0
103 Clint Backhaus B 0
103 Clint Backhaus C 0
103 Clint Backhaus D 0
105 Roger Brummont A 0
105 Roger Brummont B 0
105 Roger Brummont C 0
105 Roger Brummont D 1
106 Theresa Corbino A 0
106 Theresa Corbino B 0
106 Theresa Corbino C 0
106 Theresa Corbino D 0
107 Andrew Cornelius A 0
107 Andrew Cornelius B 0
107 Andrew Cornelius C 0
107 Andrew Cornelius D 0
108 Paul Cramer A 0
108 Paul Cramer B 0
108 Paul Cramer C 0
108 Paul Cramer D 0
122 John Cronin A 6
122 John Cronin B 5
122 John Cronin C 5
122 John Cronin D 7
Again, this could be your final query. You could stop at this point and run
this query from asp, looping through the resultset and writing the data to
the appropriate rows and columns in your html table.
Or, you could do it with sql:
Create a view using this script:
CREATE VIEW vRawCounts AS
SELECT c.Cir_ID, c.First_Name, c.Last_Name, c.Type, Count(ID) AS Total
FROM vAllCirIDsAndTypes AS c LEFT JOIN vPetCirc AS v
ON c.Cir_ID = v.CirID AND c.Type = v.Type
GROUP BY c.Cir_ID, c.First_Name, c.Last_Name, c.Type
Now we can pivot the results using this query:
SELECT Cir_ID,First_Name,Last_Name,
SUM(CASE Type WHEN 'A' THEN Total ELSE 0) As PetACirc,
SUM(CASE Type WHEN 'B' THEN Total ELSE 0) As PetBCirc,
SUM(CASE Type WHEN 'C' THEN Total ELSE 0) As PetCCirc,
SUM(CASE Type WHEN 'D' THEN Total ELSE 0) As PetDCirc
FROM vRawData
GROUP BY Cir_ID,First_Name,Last_Name
ORDER BY Cir_ID
which yields these results:
Query3 Cir_ID First_Name Last_Name PetACirc PetBCirc PetCCirc PetDCirc
100 Tony Cornelius 0 1 1 0
101 Paul DeKeyser 1 0 0 0
102 Mary Andrews 1 0 1 0
103 Clint Backhaus 0 0 0 0
105 Roger Brummont 0 0 0 1
106 Theresa Corbino 0 0 0 0
107 Andrew Cornelius 0 0 0 0
108 Paul Cramer 0 0 0 0
122 John Cronin 6 5 5 7
----------------------------------------------------------------------------
-
Three views and a pivot query vs 4 queries for every row in your CirID
table. I suspect this more complicated solution will perform better than the
subquery approach, but, the only way to be sure is by testing both.
HTH,
Bob Barrows
