Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    4

    Unanswered: Problem creating monthly sales totals

    Hi,

    I am trying to create a query that will return a list of sales people with their sales figures on a monthly basis. I'm hoping the following SQL will allow me to add as many months as I like by just adding an additional sub-select, but at the moment I'm stuck with the first month.

    select
    siebel.s_employee.last_name, (select sum(siebel.s_opty_postn.fcst_revn) from siebel.s_opty_postn where SIEBEL.S_OPTY_POSTN.FCST_CLS_DT > TO_DATE('01/01/2004', 'MM/DD/YYYY')) as jan
    from
    siebel.s_opty_postn, siebel.s_employee,
    siebel.s_postn
    where siebel.s_opty_postn.position_id = siebel.s_postn.row_id and siebel.s_postn.pr_emp_id =
    siebel.s_employee.row_id
    group by
    siebel.s_employee.last_name

    When I use this query I get the message that the sum function is not a group by expression.

    Any help gratefully received

  2. #2
    Join Date
    Dec 2003
    Posts
    2
    select
    siebel.s_employee.last_name,


    -------------------
    ----- You would have to group this section if you want to do a sum here as it is a seperate select
    --------------------
    (select sum(siebel.s_opty_postn.fcst_revn) from siebel.s_opty_postn where SIEBEL.S_OPTY_POSTN.FCST_CLS_DT > TO_DATE('01/01/2004', 'MM/DD/YYYY')) as jan
    ----------------------
    --------------------

    from
    siebel.s_opty_postn, siebel.s_employee,
    siebel.s_postn
    where siebel.s_opty_postn.position_id = siebel.s_postn.row_id and siebel.s_postn.pr_emp_id =
    siebel.s_employee.row_id
    group by
    siebel.s_employee.last_name

  3. #3
    Join Date
    Dec 2003
    Posts
    4
    Hi,

    Think I need a bit more explanation on this please, my two select statements indepenently work fine, the first will will give me a list of sales people with related sales figures and the second will give me a list of dates with associated sales values.

    select
    siebel.s_employee.last_name,
    (select sum(siebel.s_opty_postn.fcst_revn)
    from
    siebel.s_opty_postn
    where
    SIEBEL.S_OPTY_POSTN.FCST_CLS_DT > TO_DATE
    ('01/01/2004', 'MM/DD/YYYY')
    group by
    siebel.s_opty_postn.fcst_cls_dt) as jan
    from
    siebel.s_opty_postn, siebel.s_employee, siebel.s_postn
    where
    siebel.s_opty_postn.position_id = siebel.s_postn.row_id and siebel.s_postn.pr_emp_id = siebel.s_employee.row_id
    group by
    siebel.s_employee.last_name

    I still get the error message can not group by for the SUM function I tried grouping by the fcst_cls_dt in the end group by statement but this did not change the error message.

    Thanks

Posting Permissions

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