1 2 Previous Next 19 Replies Latest reply: May 3, 2012 9:30 AM by Paul Horth RSS

    Using Group by

    901973
      Hi, I am trying to execute following query and group the results by supervisor_id.I am getting an error. Here is the Query

      SELECT A.SUPERVISOR_ID, A.EMPLID, B.NAME, C.EMAIL_ADDR, (CONVERT(CHAR(10),A.JOB_ENTRY_DT,121))
      FROM PS_JOB A, PS_NAMES B, PS_EMAIL_ADDRESSES C
      WHERE A.EFFDT =
      (SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED
      WHERE A.EMPLID = A_ED.EMPLID
      AND A.EMPL_RCD = A_ED.EMPL_RCD
      AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10)
      AND A.EFFSEQ =
      (SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES
      WHERE A.EMPLID = A_ES.EMPLID
      AND A.EMPL_RCD = A_ES.EMPL_RCD
      AND A.EFFDT = A_ES.EFFDT)
      AND A.EMPLID = B.EMPLID
      AND B.EFFDT =
      (SELECT MAX(B_ED.EFFDT) FROM PS_NAMES B_ED
      WHERE B.EMPLID = B_ED.EMPLID
      AND B.NAME_TYPE = B_ED.NAME_TYPE
      AND B_ED.EFFDT <= A.EFFDT)
      AND B.EMPLID = C.EMPLID )
      GROUP BY A.SUPERVISOR_ID


      error:

      Msg 8120, Level 16, State 1, Line 1
      Column 'PS_JOB.EMPLID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

      Where did I go wrong.
        • 1. Re: Using Group by
          indra budiantho
          are you sure? cause there's no 'PS_JOB.EMPLID'
          • 2. Re: Using Group by
            901973
            Yes, what I have realized is that problem is not with the field, but with the group by clause. Still I am am not able to resolve it
            • 3. Re: Using Group by
              Paul  Horth
              Why are you using GROUP BY?

              You don't appear to be using an aggregate function (like COUNT or SUM).

              Finally, don't post unformatted code. It makes it difficult to read.
              select a.supervisor_id
                    ,a.emplid
                    ,b.name
                    ,c.email_addr
                    ,(convert(char(10)
                             ,a.job_entry_dt
                             ,121))
                from ps_job             a
                    ,ps_names           b
                    ,ps_email_addresses c
               where a.effdt = (select max(a_ed.effdt)
                                  from ps_job a_ed
                                 where a.emplid = a_ed.emplid
                                   and a.empl_rcd = a_ed.empl_rcd
                                   and a_ed.effdt <= substring(convert(char
                                                                      ,getdate()
                                                                      ,121)
                                                              ,1
                                                              ,10)
                                   and a.effseq = (select max(a_es.effseq)
                                                     from ps_job a_es
                                                    where a.emplid = a_es.emplid
                                                      and a.empl_rcd = a_es.empl_rcd
                                                      and a.effdt = a_es.effdt)
                                   and a.emplid = b.emplid
                                   and b.effdt = (select max(b_ed.effdt)
                                                    from ps_names b_ed
                                                   where b.emplid = b_ed.emplid
                                                     and b.name_type = b_ed.name_type
                                                     and b_ed.effdt <= a.effdt)
                                   and b.emplid = c.emplid)
               group by a.supervisor_id
              Edited by: Paul Horth on 03-May-2012 02:30
              • 4. Re: Using Group by
                indra budiantho
                GROUP BY a.supervisor_id, a.emplid, b.NAME, c.email_addr
                • 5. Re: Using Group by
                  901973
                  @Paul Horth      : I am new, I wont do it next time. Thanks

                  @1B: I've tried it already, it doesn't work
                  • 6. Re: Using Group by
                    Paul  Horth
                    Again, why use GROUP BY. What happens if you remove it.
                    • 7. Re: Using Group by
                      indra budiantho
                      try this:
                      /* Formatted on 2012/05/03 16:16 (Formatter Plus v4.8.8) */
                      SELECT   a.supervisor_id, a.emplid, b.NAME, c.email_addr, count(*) group_function
                          FROM ps_job a, ps_names b, ps_email_addresses c
                         WHERE a.effdt =
                                 (SELECT MAX (a_ed.effdt)
                                    FROM ps_job a_ed
                                   WHERE a.emplid = a_ed.emplid
                                     AND a.empl_rcd = a_ed.empl_rcd
                                     AND a_ed.effdt <= substring (CONVERT (CHAR, getdate (), 121), 1, 10)
                                     AND a.effseq = (SELECT MAX (a_es.effseq)
                                                       FROM ps_job a_es
                                                      WHERE a.emplid = a_es.emplid AND a.empl_rcd = a_es.empl_rcd AND a.effdt = a_es.effdt)
                                     AND a.emplid = b.emplid
                                     AND b.effdt = (SELECT MAX (b_ed.effdt)
                                                      FROM ps_names b_ed
                                                     WHERE b.emplid = b_ed.emplid AND b.name_type = b_ed.name_type AND b_ed.effdt <= a.effdt)
                                     AND b.emplid = c.emplid)
                      GROUP BY a.supervisor_id, a.emplid, b.NAME, c.email_addr
                      • 8. Re: Using Group by
                        901973
                        I am trying to sort supervisor_id , so that emplid under those supervisor id are all at a place.

                        eg

                        supervisor id emplid
                        k00001 0002
                        k00001 0003
                        k00002 00004
                        .
                        .
                        .
                        .
                        • 9. Re: Using Group by
                          901973
                          It worked. Thanks
                          • 10. Re: Using Group by
                            indra budiantho
                            Mark it as Answered then.
                            • 11. Re: Using Group by
                              Paul  Horth
                              No, no no!

                              Don't use group by to order your results!

                              Use ORDER BY.

                              It is vitally important you do not rely on group by for this.

                              Any ordering you may observe is a non-reliable side-effect of what grouping normally does.

                              It could change at any time and give you non-ordered results.

                              Edited by: Paul Horth on 03-May-2012 03:33
                              • 12. Re: Using Group by
                                Paul  Horth
                                I have noticed you have marked the question as answered.

                                Please let me know you got my last post telling you NOT to use GROUP BY to order your results.
                                • 13. Re: Using Group by
                                  chris227
                                  dont mind. next time prehaps he will find the correct forum also.
                                  See use of functions convert and GETDATE(). Transact-SQL imho

                                  Anyway:

                                  http://msdn.microsoft.com/de-de/library/ms177673.aspx

                                  GROUP BY (Transact-SQL)

                                  "The GROUP BY clause does not order the result set. Use the ORDER BY clause to order the result set"

                                  regards

                                  Edited by: chris227 on 03.05.2012 04:24
                                  • 14. Re: Using Group by
                                    indra budiantho
                                    I've changed the function:
                                    SELECT a.supervisor_id, a.emplid, b.NAME, c.email_addr, count(*) group_function
                                    .. May be he is learning about group by.
                                    1 2 Previous Next