Professional Web Applications Themes

Newbie SQL question - MySQL

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 ...

  1. #1

    Default 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 Guest

  2. #2

    Default Re: Newbie SQL question

    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" <com> wrote in message
    news:googlegroups.com... 


    AlterEgo Guest

  3. #3

    Default Re: Newbie SQL question

    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" <com> wrote: 










    > [/ref]

    DH Guest

  4. #4

    Default Re: Newbie SQL question

    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" <com> wrote in message
    news:supernews.com... 
    >
    >[/ref]


    AlterEgo Guest

  5. #5

    Default Re: Newbie SQL question

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

    Richard

    On Jan 26, 5:00 pm, "AlterEgo" <com> wrote: 



    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref][/ref]

    DH Guest

Similar Threads

  1. Newbie Question: Biz Card Template Question
    By Thomas_Porter@adobeforums.com in forum Adobe Indesign Windows
    Replies: 4
    Last Post: May 30th, 08:08 AM
  2. A newbie with a newbie question
    By dusty_davis@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 5
    Last Post: October 14th, 07:54 AM
  3. Replies: 5
    Last Post: December 3rd, 10:44 AM
  4. Pen Tool Use Question. (Embarrassingly Newbie Question)
    By Bozo Schmozo in forum Macromedia Flash
    Replies: 0
    Last Post: November 12th, 10:00 PM
  5. Newbie OO question
    By Michael Budash in forum PERL Miscellaneous
    Replies: 3
    Last Post: July 17th, 04:17 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139