Hi, Rachel,
It seems most people have missed this message.
Since you didn't give enough information in your question, in order to answer
your question, I need to make up some assumptions, which might or might not be
correct :-(
Suppose the same favsub could appear in either or both tables year9 and year10.
(From an overall DB design point of view, it would be better off not to create
distinct tables for distinct years, but have one single table with an additional
"year" column -- with this alternative design, there would be no need to create
a new table each year and no need for this question.)
Without being able to build your tables locally to test it (thus have no 100%
certainty -- apology), I suggest the following sequence of 4 SQL statements that
use a temporary table, with the same structure, to first merge year9 and year10
data to make up for your separate-year table design:
Create temporary table X (favsub int, sex char(1), . ) ;
Insert into X
( (select favsub, sex, . from year9 )
union all
(select favsub, sex, . from year10 )
) ;
Select a.favsub, count(m.sex)/count(a.*)*100, count(f.sex)/count(a.*)*100
from X a, X m, X f
where a.favsub = m.favsub
and a.favsub = f.favsub
and m.sex = 'm'
and f.sex = 'f'
order by a.favsub ;
-- you might need to play around to format the query result to meet your needs.
After executing the query, you can then issue the 4th SQL to drop temporary
table X.
Hope this helps.
Best regards,
________________________
Lin
-----Original Message-----
From: Rachel Cunliffe [mailto:[EMAIL PROTECTED]
Sent: Sunday, July 27, 2003 9:00 PM
To: [EMAIL PROTECTED]
Subject: SELECT problem
Hi,
I'm new at complex SELECT statements, so any help would be appreciated. I
need to create a summary table of counts from two tables in the database:
year9 has a stack of variables including sex and favsub (favourite subject)
year10 also has a stack of variables including sex and favsub
I'd like to output a table with the following (column %'s if possible,
otherwise counts):
sex
favsub male female
1 10% 5%
2 ...
...
At the moment, I have this as my MYSQL query:
SELECT favsub, sex, COUNT(favsub) FROM year9 GROUP BY favsub,sex ORDER BY
favsub,sex
* Problems: this is only for one of the tables, and also it's quite messy
formatting it to a nice HTML layout as there are possibly two rows for each
favourite subject, they aren't on the same row. It's also outputting the
counts, not percentages so I need to do another query to figure out the
total number of males/females.
Again, any help appreciated.
Kind regards
Rachel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
