Results 1 to 5 of 5
  1. #1
    Join Date
    May 2003
    Posts
    47

    Question Unanswered: using group by clause

    i have a query like
    select to_char(processdate, 'Month'),status
    from tablename
    group by
    to_char(processdate, 'Month'), status;


    this works fine . but in the select statement i want the whole column processdate as o/p not the month alone.
    if i change it as

    select processdate,status
    from tablename
    group by
    to_char(processdate, 'Month'), status;
    its not working
    help me

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Why did you use GROUP BY? You'd get the same with

    SELECT DISTINCT TO_CHAR(processdate, 'Month'), status
    FROM tablename;

    As of your second query - you want all processdates groupped by month; it won't work. Could you explain a little bit more what you'd like to have as a result?
    Of course, you could put it as

    SELECT DISTINCT processdate, status
    FROM tablename;

    but I'm not sure that's what you really need.

  3. #3
    Join Date
    May 2003
    Posts
    47

    the query

    the query is:
    select
    to_char(processdate,'W'), state, status, count(polnum) as nbr, sum(annualprem+annualsd) as annualprem
    from subcompenddetails
    where substr(channel,1,2)='CA' and processdate > '01-oct-2004'group by to_char(processdate,'W'),state,status

    works fine

    but instead in the select if i say just processdate - it fails

    pls help me

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I see ... in that case, your query should be
    Code:
    SELECT   processdate, state, status, COUNT (polnum) AS nbr,
             SUM (annualprem + annualsd) AS annualprem
        FROM subcompenddetails
       WHERE SUBSTR (channel, 1, 2) = 'CA' AND processdate > '01-oct-2004'
    GROUP BY processdate, state, status;
    Would that be OK?

  5. #5
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hello,

    Now that you showed your entire query, you indeed need a "group by" clause. It seems that you want to group by week : the 'W' format is "week of month", between 1 and 5. You may have many dates within a week, that's why if you group by week, you will only be able to display the weeks, not the dates. Now, "week of month" may not be precise enough for you (furthermore, using "week of month" may be tricky : a week may be part in month 5 and part in month 6...) : you can try 'WW' which is "week of year" between 1 and 53, or even better so as to know which year it is related to : 'YYYY, WW'.
    So your query would be :

    Code:
    select
    to_char(processdate,'YYYY, WW'), state, status, count(polnum) as nbr, sum(annualprem+annualsd) as annualprem
    from subcompenddetails
    where substr(channel,1,2)='CA' and processdate > '01-oct-2004'
    group by to_char(processdate,'YYYY, WW'),state,status
    Regards,

    RBARAER

Posting Permissions

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