October 5, 2007 at 10:22 am
I have two sql statements that show:
school code
school name
count of students who have used there login id
school code
school name
count of students who NOT have used there login id
How do I combine them to show:
school code
school name
count of student who have used there login id
count of student who NOT have used there login id
I thought a UNION ALL might work but it didn't.
-----------------------------------------------------------------------
select
sa.schoolc sch#,
s.schname as School,
count(*)Have_logged_In
from
users as u
inner join stugrp_active as sa on u.username = sa.suniq
inner join school as s on s.schoolc = sa.schoolc
where
u.username like '3%'
and sa.schoolc >= 300
and u.logindate is not null
group by
sa.schoolc,
s.schname
Order by
sa.schoolc
----------------------------------------------------------------
select
sa.schoolc sch#,
s.schname as School,
count(*)Not_logged_In
from
users as u
inner join stugrp_active as sa on u.username = sa.suniq
inner join school as s on s.schoolc = sa.schoolc
where
u.username like '3%'
and sa.schoolc >= 300
and u.logindate is null
group by
sa.schoolc,
s.schname
Order by
sa.schoolc
October 5, 2007 at 10:27 am
SELECT
sa.schoolc sch#,
s.schname AS School,
Have_logged_In = COUNT(CASE WHEN u.logindate IS NOT NULL THEN 1 END),
Not_logged_In = COUNT(CASE WHEN u.logindate IS NULL THEN 1 END)
FROM
users AS u
INNER JOIN stugrp_active AS sa ON u.username = sa.suniq
INNER JOIN school AS s ON s.schoolc = sa.schoolc
WHERE
u.username like '3%'
AND sa.schoolc >= 300
GROUP BY
sa.schoolc,
s.schname
ORDER BY
sa.schoolc
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 5, 2007 at 10:29 am
Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply