ethanjohnsons
asked on
Combining cfqueries with cfoutput
<cfquery name="q1", ....>
select VOLDEPT, count (VOLDEPT) as deptCount
from VolunteerDemo
group by VOLDEPT
order by VOLDEPT
</cfquery>
<cfquery name="q2", ....>
select VOLDEPT, count (VOLINFLUENZA) as fluCount
from VolunteerDemo d, VolunteerMedical m
where d.VOLID = m.VOLID
and VOLINFLUENZA between '01/01/2001' and '10/01/2006'
group by VOLDEPT
order by VOLDEPT
</cfquery>
What I want is: the count of dept (from q1) and then count of the flu (from q2).
so, it gives, i.e.
VOLDEPT q1.deptCount q2.fluCount
BER-PD 5 1
BLOSSOM SPOT 4 4
BPK-FD 20 1
CONTRACT 12 0 ==> this is where the count of dept exists, but no flu count exists.
bla...
thx
select VOLDEPT, count (VOLDEPT) as deptCount
from VolunteerDemo
group by VOLDEPT
order by VOLDEPT
</cfquery>
<cfquery name="q2", ....>
select VOLDEPT, count (VOLINFLUENZA) as fluCount
from VolunteerDemo d, VolunteerMedical m
where d.VOLID = m.VOLID
and VOLINFLUENZA between '01/01/2001' and '10/01/2006'
group by VOLDEPT
order by VOLDEPT
</cfquery>
What I want is: the count of dept (from q1) and then count of the flu (from q2).
so, it gives, i.e.
VOLDEPT q1.deptCount q2.fluCount
BER-PD 5 1
BLOSSOM SPOT 4 4
BPK-FD 20 1
CONTRACT 12 0 ==> this is where the count of dept exists, but no flu count exists.
bla...
thx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I dont see why you cant do this in one query:
select VOLDEPT, count (VOLDEPT) as deptCount,
(select count (VOLINFLUENZA) as fluCount
from VolunteerMedical m
where vd.VOLID = m.VOLID
and VOLINFLUENZA between '01/01/2001' and '10/01/2006'
) as flucount
from VolunteerDemo vd
group by VOLDEPT
order by VOLDEPT
select VOLDEPT, count (VOLDEPT) as deptCount,
(select count (VOLINFLUENZA) as fluCount
from VolunteerMedical m
where vd.VOLID = m.VOLID
and VOLINFLUENZA between '01/01/2001' and '10/01/2006'
) as flucount
from VolunteerDemo vd
group by VOLDEPT
order by VOLDEPT
ASKER
ok,
Column 'vd.VOLID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
select vd.VOLDEPT, count (vd.VOLDEPT) as deptCount,
(select count (VOLINFLUENZA) as fluCount
from VolunteerMedical m
where vd.VOLID = m.VOLID
and VOLINFLUENZA between '01/01/2001' and '10/01/2006'
) as flucount
from VolunteerDemo vd
group by vd.VOLDEPT
order by vd.VOLDEPT
I think I am missing a simple thing here.
Any clue?
thx
Column 'vd.VOLID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
select vd.VOLDEPT, count (vd.VOLDEPT) as deptCount,
(select count (VOLINFLUENZA) as fluCount
from VolunteerMedical m
where vd.VOLID = m.VOLID
and VOLINFLUENZA between '01/01/2001' and '10/01/2006'
) as flucount
from VolunteerDemo vd
group by vd.VOLDEPT
order by vd.VOLDEPT
I think I am missing a simple thing here.
Any clue?
thx
select vd.VOLDEPT, count (vd.VOLDEPT) as deptCount,
(select count (VOLINFLUENZA) as fluCount
from VolunteerMedical m
where vd.VOLID = m.VOLID
and VOLINFLUENZA between '01/01/2001' and '10/01/2006'
) as flucount
from VolunteerDemo vd
group by vd.VOLDEPT, (select count (VOLINFLUENZA) as fluCount
from VolunteerMedical m
where vd.VOLID = m.VOLID
and VOLINFLUENZA between '01/01/2001' and '10/01/2006'
)
order by vd.VOLDEPT, (select count (VOLINFLUENZA) as fluCount
from VolunteerMedical m
where vd.VOLID = m.VOLID
and VOLINFLUENZA between '01/01/2001' and '10/01/2006'
)
(select count (VOLINFLUENZA) as fluCount
from VolunteerMedical m
where vd.VOLID = m.VOLID
and VOLINFLUENZA between '01/01/2001' and '10/01/2006'
) as flucount
from VolunteerDemo vd
group by vd.VOLDEPT, (select count (VOLINFLUENZA) as fluCount
from VolunteerMedical m
where vd.VOLID = m.VOLID
and VOLINFLUENZA between '01/01/2001' and '10/01/2006'
)
order by vd.VOLDEPT, (select count (VOLINFLUENZA) as fluCount
from VolunteerMedical m
where vd.VOLID = m.VOLID
and VOLINFLUENZA between '01/01/2001' and '10/01/2006'
)
ASKER
I am getting:
Server: Msg 144, Level 15, State 1, Line 15
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.
Server: Msg 144, Level 15, State 1, Line 15
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.
select vd.VOLDEPT, count (vd.VOLDEPT) as deptCount,
(select count (VOLINFLUENZA) as fluCount
from VolunteerMedical m
where vd.VOLID = m.VOLID
and VOLINFLUENZA between '01/01/2001' and '10/01/2006'
) as flucount
from VolunteerDemo vd
group by vd.VOLDEPT, vd.VOLID
order by vd.VOLDEPT, vd.VOLID
(select count (VOLINFLUENZA) as fluCount
from VolunteerMedical m
where vd.VOLID = m.VOLID
and VOLINFLUENZA between '01/01/2001' and '10/01/2006'
) as flucount
from VolunteerDemo vd
group by vd.VOLDEPT, vd.VOLID
order by vd.VOLDEPT, vd.VOLID
ASKER
It just gives 1 and 0 all the way:
ADMINISTRATION 1 0
AIR-FD 1 0
AIR-FD 1 0
AIR-FD 1 0
AIR-FD 1 0
AIR-FD 1 0
AIR-FD 1 0
AIR-FD 1 0
AIR-FD 1 0
AIR-FD 1 0
AIR-FD 1 0
bla...
I think we need to look for cf solution if possible.
ADMINISTRATION 1 0
AIR-FD 1 0
AIR-FD 1 0
AIR-FD 1 0
AIR-FD 1 0
AIR-FD 1 0
AIR-FD 1 0
AIR-FD 1 0
AIR-FD 1 0
AIR-FD 1 0
AIR-FD 1 0
bla...
I think we need to look for cf solution if possible.
From your post above it looks like the subquery is returning the right stuff but needs to be grouped, i.e. 1 row = 1 count
Sorry this is kind of hard as we dont have the tables to try it and give you a definitive answer:
select vd.VOLDEPT, count (vd.VOLDEPT) as deptCount,
(select count (VOLINFLUENZA) as fluCount
from VolunteerMedical m
where vd.VOLID = m.VOLID
and VOLINFLUENZA between '01/01/2001' and '10/01/2006'
group by vd.VOLDEPT, vd.VOLID
order by vd.VOLDEPT, vd.VOLID
) as flucount
from VolunteerDemo vd
group by vd.VOLDEPT, vd.VOLID
order by vd.VOLDEPT, vd.VOLID
Sorry this is kind of hard as we dont have the tables to try it and give you a definitive answer:
select vd.VOLDEPT, count (vd.VOLDEPT) as deptCount,
(select count (VOLINFLUENZA) as fluCount
from VolunteerMedical m
where vd.VOLID = m.VOLID
and VOLINFLUENZA between '01/01/2001' and '10/01/2006'
group by vd.VOLDEPT, vd.VOLID
order by vd.VOLDEPT, vd.VOLID
) as flucount
from VolunteerDemo vd
group by vd.VOLDEPT, vd.VOLID
order by vd.VOLDEPT, vd.VOLID
oops sorry,
select vd.VOLDEPT, count (vd.VOLDEPT) as deptCount,
(select count (VOLINFLUENZA) as fluCount
from VolunteerMedical m
where vd.VOLID = m.VOLID
and VOLINFLUENZA between '01/01/2001' and '10/01/2006'
group by m.VOLID
order by m.VOLID
) as flucount
from VolunteerDemo vd
group by vd.VOLDEPT, vd.VOLID
order by vd.VOLDEPT, vd.VOLID
select vd.VOLDEPT, count (vd.VOLDEPT) as deptCount,
(select count (VOLINFLUENZA) as fluCount
from VolunteerMedical m
where vd.VOLID = m.VOLID
and VOLINFLUENZA between '01/01/2001' and '10/01/2006'
group by m.VOLID
order by m.VOLID
) as flucount
from VolunteerDemo vd
group by vd.VOLDEPT, vd.VOLID
order by vd.VOLDEPT, vd.VOLID
ASKER
I am getting this:
Server: Msg 1033, Level 15, State 1, Line 9
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
Server: Msg 1033, Level 15, State 1, Line 9
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
ok take out order by from the inner select:
select vd.VOLDEPT, count (vd.VOLDEPT) as deptCount,
(select count (VOLINFLUENZA) as fluCount
from VolunteerMedical m
where vd.VOLID = m.VOLID
and VOLINFLUENZA between '01/01/2001' and '10/01/2006'
group by m.VOLID
) as flucount
from VolunteerDemo vd
group by vd.VOLDEPT, vd.VOLID
order by vd.VOLDEPT, vd.VOLID
select vd.VOLDEPT, count (vd.VOLDEPT) as deptCount,
(select count (VOLINFLUENZA) as fluCount
from VolunteerMedical m
where vd.VOLID = m.VOLID
and VOLINFLUENZA between '01/01/2001' and '10/01/2006'
group by m.VOLID
) as flucount
from VolunteerDemo vd
group by vd.VOLDEPT, vd.VOLID
order by vd.VOLDEPT, vd.VOLID
ASKER
It still returns:
AIR-FD 1 NULL
AIR-FD 1 NULL
AIR-FD 1 NULL
AIR-FD 1 NULL
AIR-FD 1 NULL
AIR-FD 1 NULL
AIR-FD 1 NULL
AIR-FD 1 NULL
AIR-FD 1 NULL
AIR-FD 1 NULL
AIR-FD 1 NULL
AIR-FD 1 NULL
AIR-FD 1 NULL
AIR-FD 1 NULL
AIR-FD 1 NULL
AIR-FD 1 NULL
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thx much
ASKER
<cfquery dbtype="query" name="qofq">
select * from q1, q2
where q1.VOLDEPT = q2.VOLDEPT
</cfquery>
I think this will not cover this case.
CONTRACT 12 0 ==> this is where the count of dept exists, but no flu count exists.
==> q1 has the count, but q2 doesn't.
any ideas?