Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2006
    Posts
    87

    Unanswered: problem with grouping

    have this table

    id, subjectid, q1, q2
    1 1000 1 1
    2 2000 4 3
    3 1000 2 1
    4 2000 3 1

    I need to create a report wherein i need to group by subjectid and count how many 1's and 2's etc. the result would be

    1000
    VALUES countQ1 countQ2
    1 1 2
    2 1 0
    3 1 0
    4 0 0

    2000
    VALUES countQ1 countQ2
    1 1 1
    2 1 0
    3 1 1
    4 0 0

    thanks for any help

    alex

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT subjectid
         , `value`
         , COUNT(valueQ1) AS countQ1
         , COUNT(valueQ2) AS countQ2
      FROM ( SELECT daTable.subjectid
                  , qs.v        AS `value`
                  , daTable.q1  AS valueQ1
                  , NULL        AS valueQ2
             
               FROM ( SELECT q1 AS v
                        FROM daTable
                      UNION -- removes dupes
                      SELECT q2
                        FROM daTable ) AS qs
             LEFT OUTER
               JOIN daTable
                 ON daTable.q1 = qs.v
             UNION ALL -- keep dupes!!
             SELECT daTable.subjectid
                  , qs.v       AS `value`
                  , NULL        AS valueQ1
                  , daTable.q2  AS valueQ2
               FROM ( SELECT q1 AS v
                        FROM daTable
                      UNION -- removes dupes
                      SELECT q2
                        FROM daTable ) AS qs
             LEFT OUTER
               JOIN daTable
                 ON daTable.q2 = qs.v
           ) AS d
    GROUP
        BY subjectid
         , `value`
    that's more than twice as complicated as it needs to be, and you can blame q1, q2 for that (does not conform to 1NF)

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2006
    Posts
    87
    thanks will try this and really that's complicated next time will be following the rules...

Posting Permissions

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