Combine a lot of 1

[jstreich]

I'm looking to improve the queries I have in a statistics page from a survey. I have a lot of queries that look like:

    select case 
     when(stream=0) then 'Do not Use, Not Interested' 
     when(stream=1) then 'Do not User, Would Like To' 
     when(stream=2) then 'Use in Some Courses' 
     when(stream=3) then 'Use in all courses' 
     else 'Undecided' 
    end as title,
    count(*) as num 
    from techsur group by stream order by stream

    select case 
     when(pod=0) then 'Do not Use, Not Interested' 
     when(pod=1) then 'Do not User, Would Like To' 
     when(pod=2) then 'Use in Some Courses' 
     when(pod=3) then 'Use in all courses' 
     else 'Undecided' 
    end as title,
    count(*) as num 
    from techsur group by pod order by pod

I'd like to be able to run these queries together (into a single query ?) to get a result table that looks:

  title                    | num_pod | num_stream
Do not use, not interested |   5     |   20
[red]. . .[/red]

Any thoughts or suggestions would be appreciated.

[plug=shameless]

http://game-master.us/phpx-3.4.0/

[/plug]

 

[hoinz]

I think you will have to join two subqueries,
something like:

select a.title, a.num as num_pod, b.num as num_stream
from
( your 1st select here ) a,
( your 2nd select here ) b
where a.title = b.title

btw, it might be faster if you select numbers (instead of text given by 'case') in the subqueries, and do the translation to text only in the outer query.

hope this helps

 

[jstreich]

I'll try that.
I'm doing this same for 12 fields, and a number of other similar queries before and after it -- so any performance increase would be a great help.

[plug=shameless]

http://game-master.us/phpx-3.4.0/

[/plug]

 

[hoinz]

One more thought:
At first I did not pay attention to your 'order by' clause;
you can't use it in the inner queries, but only once in the outer query.
If you want to order by numbers (assuming they are the same for all tables), then this is one more reason to translate to text only at the end. (Apart from the performance gain by not doing it that often.)

regards

 

[jstreich]

The order by will work because it's the shared field. (the one in the case statement). So I'll just sort on the big one. It worked brilliantly. Star for you.

[plug=shameless]

http://game-master.us/phpx-3.4.0/

[/plug]

 

[hoinz]

glad it helped,
thanks

 

[jstreich]

Actually now my query ends up with an under count, any help would be appreciated.

Current Query:

select 
  case 
   when(audio.v=-1) then 'Not Familiar With'
   when(audio.v=0) then 'Do Not Use, Not Interested'
   when(audio.v=1) then 'Do Not Use, Would Like To'
   when(audio.v=2) then 'Use in Some Courses'
   when(audio.v=3) then 'Use in All Courses'
  end as title,
  audio.num as audion,
  email.num as emailn,
  net.num as netn,
  media.num as median,
  ppt.num as pptn,
  wtut.num as wtutn,
  d2l.num as d2ln,
  pod.num as podn,
  stream.num as streamn,
  vc.num as vcn,
  tape.num as tapen,
  vppt.num as vpptn,
  ((audio.num + email.num + net.num + media.num + ppt.num + wtut.num + d2l.num + pod.num + stream.num + vc.num + tape.num + vppt.num)/12) as avgn
from 
  (select audio as v, count(*) as num from techsur group by audio order by audio) audio,
  (select email as v, count(*) as num from techsur group by email order by email) email,
  (select net as v, count(*) as num from techsur group by net order by net) net,
  (select media as v, count(*) as num from techsur group by media order by media) media,
  (select ppt as v, count(*) as num from techsur group by ppt order by ppt) ppt,
  (select wtut as v, count(*) as num from techsur group by wtut order by wtut) wtut,
  (select d2l as v, count(*) as num from techsur group by d2l order by d2l) d2l,
  (select pod as v, count(*) as num from techsur group by pod order by pod) pod,
  (select stream as v, count(*) as num from techsur group by stream order by stream) stream,
  (select vc as v, count(*) as num from techsur group by vc order by vc) vc,
  (select tape as v, count(*) as num from techsur group by tape order by tape) tape,
  (select vppt as v, count(*) as num from techsur group by vppt order by vppt) vppt
where
  audio.v=email.v and 
  audio.v=net.v and 
  audio.v=media.v and
  audio.v=ppt.v and
  audio.v=wtut.v and 
  audio.v=d2l.v and
  audio.v=pod.v and
  audio.v=stream.v and
  audio.v=vc.v and
  audio.v=tape.v and
  audio.v=vppt.v

Changing the "and" to "or" of course creates an over count, but the records don't show up unless there is one instance of each value for each item. Any help would be appreciated.

[plug=shameless]

http://game-master.us/phpx-3.4.0/

[/plug]

 

[hoinz]

An outer join might help here. (Actually this is what an outer join was invented for.)
Is there a subquery among your audio, email, net, ... that will in any case contain all of your values for v?
If so, outer join with this subquery.
If not so, it may be necessary to create one more subquery for this purpose.

hope this helps

 

[hoinz]

for example, if audio had all of your values for v,
you would change your where condition to:
where
audio.v=email.v(+) and
audio.v=net.v(+) and
audio.v=media.v(+) and
...

 

[jstreich]

Ah!
I see.

[plug=shameless]

http://game-master.us/phpx-3.4.0/

[/plug]