Newbie SQL question

Sorry if this has already been asked before; I wasn't sure how to
formulate a search for what I'm trying to do.

I would like to do something similar to the following:

Lets say that I have a column that can contain four distinct values:

For example; What is your class level? (Freshman, Sophomore, Junior,
Senior)

All of the responses would be stored in the column CLASS_LEVEL in the
table RESULTS.

How could I produce the counts of each in one request?

I am trying to do something like this.

Select
count(CLASS_LEVEL) where CLASS_LEVEL = "Freshman" as FRCOUNT,
count(CLASS_LEVEL) where CLASS_LEVEL = "Sophomore" as SOCOUNT,
count(CLASS_LEVEL) where CLASS_LEVEL = "Junior" as JCOUNT,
count(CLASS_LEVEL) where CLASS_LEVEL = "Senior" as SECOUNT
from RESULTS;

I know that I can do something like the following:

SELECT CLASS_LEVEL, COUNT(*) as "Number in class level"
FROM results
GROUP BY CLASS_LEVEL;

But I am trying to get all the information on one line.

Thanks in Advance.

DH,

I haven't tried this specifically with MySQL, but it is ANSI compliant and
should work.

select
sum(Freshman) SumOfFreshman
, sum(Sophmore) SumOfSophmore
, sum(Junior) SumOfJunior
, sum(Senior) SumOf Senior
from
(
select
when CLASS_LEVEL = 'Freshman' then 1 else 0 end as Freshman
, when CLASS_LEVEL = 'Sophmore' then 1 else 0 end as Sophmore
, when CLASS_LEVEL = 'Junior' then 1 else 0 end as Junior
, when CLASS_LEVEL = 'Senior' then 1 else 0 end as Senior
from Results
) t

-- Bill

"DH" <sheehan_rs@yahoo.com> wrote in message
news:1169842940.564178.60380@a75g2000cwd.googlegro ups.com...
> Sorry if this has already been asked before; I wasn't sure how to
> formulate a search for what I'm trying to do.
>
> I would like to do something similar to the following:
>
> Lets say that I have a column that can contain four distinct values:
>
> For example; What is your class level? (Freshman, Sophomore, Junior,
> Senior)
>
> All of the responses would be stored in the column CLASS_LEVEL in the
> table RESULTS.
>
> How could I produce the counts of each in one request?
>
> I am trying to do something like this.
>
> Select
> count(CLASS_LEVEL) where CLASS_LEVEL = "Freshman" as FRCOUNT,
> count(CLASS_LEVEL) where CLASS_LEVEL = "Sophomore" as SOCOUNT,
> count(CLASS_LEVEL) where CLASS_LEVEL = "Junior" as JCOUNT,
> count(CLASS_LEVEL) where CLASS_LEVEL = "Senior" as SECOUNT
> from RESULTS;
>
> I know that I can do something like the following:
>
> SELECT CLASS_LEVEL, COUNT(*) as "Number in class level"
> FROM results
> GROUP BY CLASS_LEVEL;
>
> But I am trying to get all the information on one line.
>
> Thanks in Advance.
>

Thanks Bill; however, I am not having any luck with the "select when"
phrase.

I even tried doing a similar thing in Oracle SQL Developer:

Do you have any other ideas?

TIA,

Richard

<oracle sql developer>
describe employees;
Name Null Type



------------------------------ --------
-----------------------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)



FIRST_NAME VARCHAR2(20)



LAST_NAME NOT NULL VARCHAR2(25)



EMAIL NOT NULL VARCHAR2(25)



PHONE_NUMBER VARCHAR2(20)



HIRE_DATE NOT NULL DATE



JOB_ID NOT NULL VARCHAR2(10)



SALARY NUMBER(8,2)



COMMISSION_PCT NUMBER(2,2)



MANAGER_ID NUMBER(6)



DEPARTMENT_ID NUMBER(4)




11 rows selected

select job_id, count(job_id)
from employees
group by job_id;

JOB_ID COUNT(JOB_ID)
---------- ----------------------
..
..
AD_VP 2
FI_ACCOUNT 5
..
..

19 rows selected

select when job_id = 'FI_ACCOUNT' then 1 else 0 end as FI_ACCOUNT
from employees;

Error starting at line 1 in command:
select when job_id = 'FI_ACCOUNT' then 1 else 0 end as FI_ACCOUNT
from employees
Error at Command Line:1 Column:19
Error report:
SQL Error: ORA-00923: FROM keyword not found where expected
00923. 00000 - "FROM keyword not found where expected"
</oracle sql developer>


On Jan 26, 1:50 pm, "AlterEgo" <altereg...@dslextreme.com> wrote:
> DH,
>
> I haven't tried this specifically with MySQL, but it is ANSI compliant and
> should work.
>
> select
> sum(Freshman) SumOfFreshman
> , sum(Sophmore) SumOfSophmore
> , sum(Junior) SumOfJunior
> , sum(Senior) SumOf Senior
> from
> (
> select
> when CLASS_LEVEL = 'Freshman' then 1 else 0 end as Freshman
> , when CLASS_LEVEL = 'Sophmore' then 1 else 0 end as Sophmore
> , when CLASS_LEVEL = 'Junior' then 1 else 0 end as Junior
> , when CLASS_LEVEL = 'Senior' then 1 else 0 end as Senior
> from Results
> ) t
>
> -- Bill
>
> "DH" <sheehan...@yahoo.com> wrote in messagenews:1169842940.564178.60380@a75g2000cwd.go oglegroups.com...
>
> > Sorry if this has already been asked before; I wasn't sure how to
> > formulate a search for what I'm trying to do.
>
> > I would like to do something similar to the following:
>
> > Lets say that I have a column that can contain four distinct values:
>
> > For example; What is your class level? (Freshman, Sophomore, Junior,
> > Senior)
>
> > All of the responses would be stored in the column CLASS_LEVEL in the
> > table RESULTS.
>
> > How could I produce the counts of each in one request?
>
> > I am trying to do something like this.
>
> > Select
> > count(CLASS_LEVEL) where CLASS_LEVEL = "Freshman" as FRCOUNT,
> > count(CLASS_LEVEL) where CLASS_LEVEL = "Sophomore" as SOCOUNT,
> > count(CLASS_LEVEL) where CLASS_LEVEL = "Junior" as JCOUNT,
> > count(CLASS_LEVEL) where CLASS_LEVEL = "Senior" as SECOUNT
> > from RESULTS;
>
> > I know that I can do something like the following:
>
> > SELECT CLASS_LEVEL, COUNT(*) as "Number in class level"
> > FROM results
> > GROUP BY CLASS_LEVEL;
>
> > But I am trying to get all the information on one line.
>
> > Thanks in Advance.

Sorry Dh,

I somehow deleted the "case" command in the sub-query.

select
sum(Freshman) SumOfFreshman
, sum(Sophmore) SumOfSophmore
, sum(Junior) SumOfJunior
, sum(Senior) SumOfSenior
from
(
select
case when CLASS_LEVEL = 'Freshman' then 1 else 0 end as Freshman
, case when CLASS_LEVEL = 'Sophmore' then 1 else 0 end as Sophmore
, case when CLASS_LEVEL = 'Junior' then 1 else 0 end as Junior
, case when CLASS_LEVEL = 'Senior' then 1 else 0 end as Senior
from results
) t

-- Bill

"AlterEgo" <alterego55@dslextreme.com> wrote in message
news:12rkqgq33c1m98e@corp.supernews.com...
> DH,
>
> I haven't tried this specifically with MySQL, but it is ANSI compliant and
> should work.
>
> select
> sum(Freshman) SumOfFreshman
> , sum(Sophmore) SumOfSophmore
> , sum(Junior) SumOfJunior
> , sum(Senior) SumOf Senior
> from
> (
> select
> when CLASS_LEVEL = 'Freshman' then 1 else 0 end as Freshman
> , when CLASS_LEVEL = 'Sophmore' then 1 else 0 end as Sophmore
> , when CLASS_LEVEL = 'Junior' then 1 else 0 end as Junior
> , when CLASS_LEVEL = 'Senior' then 1 else 0 end as Senior
> from Results
> ) t
>
> -- Bill
>
> "DH" <sheehan_rs@yahoo.com> wrote in message
> news:1169842940.564178.60380@a75g2000cwd.googlegro ups.com...
>> Sorry if this has already been asked before; I wasn't sure how to
>> formulate a search for what I'm trying to do.
>>
>> I would like to do something similar to the following:
>>
>> Lets say that I have a column that can contain four distinct values:
>>
>> For example; What is your class level? (Freshman, Sophomore, Junior,
>> Senior)
>>
>> All of the responses would be stored in the column CLASS_LEVEL in the
>> table RESULTS.
>>
>> How could I produce the counts of each in one request?
>>
>> I am trying to do something like this.
>>
>> Select
>> count(CLASS_LEVEL) where CLASS_LEVEL = "Freshman" as FRCOUNT,
>> count(CLASS_LEVEL) where CLASS_LEVEL = "Sophomore" as SOCOUNT,
>> count(CLASS_LEVEL) where CLASS_LEVEL = "Junior" as JCOUNT,
>> count(CLASS_LEVEL) where CLASS_LEVEL = "Senior" as SECOUNT
>> from RESULTS;
>>
>> I know that I can do something like the following:
>>
>> SELECT CLASS_LEVEL, COUNT(*) as "Number in class level"
>> FROM results
>> GROUP BY CLASS_LEVEL;
>>
>> But I am trying to get all the information on one line.
>>
>> Thanks in Advance.
>>
>
>

Thanks,
This works for what I'm trying to do.

Richard

On Jan 26, 5:00 pm, "AlterEgo" <altereg...@dslextreme.com> wrote:
> Sorry Dh,
>
> I somehow deleted the "case" command in the sub-query.
>
> select
> sum(Freshman) SumOfFreshman
> , sum(Sophmore) SumOfSophmore
> , sum(Junior) SumOfJunior
> , sum(Senior) SumOfSenior
> from
> (
> select
> case when CLASS_LEVEL = 'Freshman' then 1 else 0 end as Freshman
> , case when CLASS_LEVEL = 'Sophmore' then 1 else 0 end as Sophmore
> , case when CLASS_LEVEL = 'Junior' then 1 else 0 end as Junior
> , case when CLASS_LEVEL = 'Senior' then 1 else 0 end as Senior
> from results
> ) t
>
> -- Bill
>
> "AlterEgo" <altereg...@dslextreme.com> wrote in messagenews:12rkqgq33c1m98e@corp.supernews.com...
>
> > DH,
>
> > I haven't tried this specifically with MySQL, but it is ANSI compliant and
> > should work.
>
> > select
> > sum(Freshman) SumOfFreshman
> > , sum(Sophmore) SumOfSophmore
> > , sum(Junior) SumOfJunior
> > , sum(Senior) SumOf Senior
> > from
> > (
> > select
> > when CLASS_LEVEL = 'Freshman' then 1 else 0 end as Freshman
> > , when CLASS_LEVEL = 'Sophmore' then 1 else 0 end as Sophmore
> > , when CLASS_LEVEL = 'Junior' then 1 else 0 end as Junior
> > , when CLASS_LEVEL = 'Senior' then 1 else 0 end as Senior
> > from Results
> > ) t
>
> > -- Bill
>
> > "DH" <sheehan...@yahoo.com> wrote in message
> >news:1169842940.564178.60380@a75g2000cwd.googlegr oups.com...
> >> Sorry if this has already been asked before; I wasn't sure how to
> >> formulate a search for what I'm trying to do.
>
> >> I would like to do something similar to the following:
>
> >> Lets say that I have a column that can contain four distinct values:
>
> >> For example; What is your class level? (Freshman, Sophomore, Junior,
> >> Senior)
>
> >> All of the responses would be stored in the column CLASS_LEVEL in the
> >> table RESULTS.
>
> >> How could I produce the counts of each in one request?
>
> >> I am trying to do something like this.
>
> >> Select
> >> count(CLASS_LEVEL) where CLASS_LEVEL = "Freshman" as FRCOUNT,
> >> count(CLASS_LEVEL) where CLASS_LEVEL = "Sophomore" as SOCOUNT,
> >> count(CLASS_LEVEL) where CLASS_LEVEL = "Junior" as JCOUNT,
> >> count(CLASS_LEVEL) where CLASS_LEVEL = "Senior" as SECOUNT
> >> from RESULTS;
>
> >> I know that I can do something like the following:
>
> >> SELECT CLASS_LEVEL, COUNT(*) as "Number in class level"
> >> FROM results
> >> GROUP BY CLASS_LEVEL;
>
> >> But I am trying to get all the information on one line.
>
> >> Thanks in Advance.