Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2010

    Unanswered: Combining two queries?

    I'm not sure the best way to do what I am trying to do. I have not used SQL in a while so bear wit me.

    This is my base query. It returns the number of users at a school using Macs, like this:

    School Name, Number of Macs
    A , 1
    B , 20
    C , 7


    SELECT fileAs as "School Name",count(Distinct reg.userid) as "Number of Macs"
    FROM RegHist reg
    JOIN students s ON reg.userid = s.userid
    INNER JOIN school_info school ON school.schoolid = s.schoolid
    where productName = 'mac'
    group by sch.schoolid, sch.fileAs
    order by fileAs

    So I can execute the query and it works as it should. Now I want to compare that number with the whole number of registrations. Basically the same query without the where clause. And then i want to take the percentage of one over the other. For my final result i would like something like this.

    School Name, Number Of Macs, Total Registration, Percentage of Mac Users
    A, 10, 100, 10%

    I was under the impression i would use a join for this? Not sure someone please advise.

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    Quote Originally Posted by inwoodpcpro View Post
    So I can execute the query and it works as it should.
    i don't believe you, at least not with the query you've shown us, because it has glaring syntax errors (you incorrectly use the "sch" table alias in the GROUP BY, when the school_info table is called "school")

    no, you don't need another join to get your stats
    SELECT fileAs AS "School Name"
         , COUNT(
             CASE WHEN productName = 'mac'
                  THEN 'fanboy'
                  ELSE NULL END ) AS "Number of Macs"
         , COUNT(*) AS "Total Registration"
      FROM RegHist reg 
      JOIN students s 
        ON s.userid = reg.userid
      JOIN school_info school 
        ON school.schoolid = s.schoolid
       BY sch.schoolid
        , sch.fileAs
        BY fileAs | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2010
    I changed it to make it more readable here. In my original code it was sch but i thought i would make it school to make it easier to understand for the post.

Posting Permissions

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