Results 1 to 7 of 7

Thread: Grouping pblm

  1. #1
    Join Date
    Jan 2004
    Posts
    9

    Unanswered: Grouping pblm

    Hi,

    I have a query which returns several data fields and one of the grouping criteria is the time.
    The data format for the time field resembles this: "10.11.2005 15:45:37" .. which is 'date.month.year calltime'.
    I want the query to group data by the hour, therefore i wrote the query like this: Left([AllCalls.CALLTIME],13) AS HourlyCallTime, the HourlyCallTime field shows the data in this format: 10.11.2005 15, however, the grouping is not done. It only groups properly when I do this: Left([AllCalls.CALLTIME],12) AS HourlyCallTime, but then the problem is that the HourlyCallTime field does not show the proper format, it only displays this much: '10.11.2005 1'

    I hope some1 can help me out

    Thks

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I speak Oracle SQL; this is, as far as I can tell, not a language I know, but perhaps this piece of advice will help you ...

    In Oracle, date format you wrote as an example (10.11.2005 15:45:37) is only one (of many possible) representations of a date column. Dates are stored as a number, and it is up to the developer to choose format he wants to present data to the end user.

    Now, in Oracle, you should do this: FIRST format date column to desired format, and THEN write string functions on it.

    For example, it would look like this:
    - First part of the solution:
    SELECT TO_CHAR(date_column, 'dd.mm.yyyy hh:mi:ss') FROM ...

    - Second part of the solution:
    SELECT SUBSTR(TO_CHAR(date_column, 'dd.mm.yyyy hh:mi:ss'), 1, 13) FROM ...

    I really wouldn't know is this the case in your database, but - if nothing else shows up - you could try with this.

  3. #3
    Join Date
    Jan 2004
    Posts
    9

    Lightbulb

    Hello Littlefoot,

    Thks 4 the quick rep, i've been working on it but now seem to be having sum other pblm,
    my query

    SELECT Zones.Zone, Left([AllCalls.CALLTIME],13) AS HourlyCallTime, SCCount.Connected, ((SCCount.Connected/(UCCount.NotConnected+SCCount.Connected)*100)) AS Val
    FROM UCCount INNER JOIN (SCCount INNER JOIN ((Zones INNER JOIN AllCalls ON Zones.Zone = AllCalls.PREFIX) INNER JOIN AllCallsBack ON AllCalls.CALLID = AllCallsBack.CALLID) ON SCCount.PREFIX = AllCalls.PREFIX) ON UCCount.PREFIX = AllCalls.PREFIX
    WHERE (((AllCalls.PREFIX)=[Zones].[Zone] And (AllCalls.PREFIX)=[SCCount].[PREFIX] And (AllCalls.PREFIX)=[UCCount].[PREFIX]))
    GROUP BY Zones.Zone, Left([AllCalls.CALLTIME],13), SCCount.Connected, UCCount.NotConnected, AllCalls.PREFIX;


    is not grouping all the calls, it's separating the connected and not connected such that am having twice the same row of data.
    When I remove the SCCount.Connected and UCCount.NotConnected, it doesn't run, comes up with "query does not include the specified xpression .."


  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Hm, it seems that you, actually, do not want to GROUP data, but BREAK output on the hour. I'd say that use of a GROUP BY is meaningless if there's no aggregate function (such as MAX or AVG or COUNT) in the SELECT statement.

    I don't know the tool you use (do you run this query on command prompt or in a reporting tool); if it is some kind of a report builder, you might want to use master-detail blocks of data.

    On command prompt, all you can do is use of an ORDER BY clause and, eventually, use of (as Oracle provides) some kind of a BREAK command which will visually break data on the screen. Something like this:
    Code:
    SQL> break on hire_year
    SQL> select to_char(hiredate, 'yyyy') hire_year, ename, hiredate
      2  from emp
      3  order by 1, 2;
    
    HIRE ENAME      HIREDATE
    ---- ---------- --------
    1980 SMITH      17.12.80
    1981 ALLEN      20.02.81
         BLAKE      01.05.81
         CLARK      09.06.81
         FORD       03.12.81
         JAMES      03.12.81
         JONES      02.04.81
         KING       17.11.81
         MARTIN     28.09.81
         TURNER     08.09.81
         WARD       22.02.81
    1982 MILLER     23.01.82
    
    12 rows selected.
    
    SQL>

  5. #5
    Join Date
    Jan 2004
    Posts
    9

    Lightbulb

    But I do want to group the data by zones and by the hour, but the query wouldn't run unless i include the connected n notconnected as part of the grouping criteria as well which is messing it up

  6. #6
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    I assume Connected and NotConnectedand are some times and you calculete percentage. So if you group data by Zones etc. why don't you summarize times?
    Code:
    SELECT 
         Zones.Zone, 
         Left([AllCalls.CALLTIME],13) AS HourlyCallTime, 
         sum(SCCount.Connected),
         ((sum(SCCount.Connected)/(sum(UCCount.NotConnected+SCCount.Connected))*100)) AS Val
    FROM UCCount INNER JOIN (SCCount INNER JOIN ((Zones INNER JOIN AllCalls ON Zones.Zone = AllCalls.PREFIX) INNER JOIN AllCallsBack ON AllCalls.CALLID = AllCallsBack.CALLID) ON SCCount.PREFIX = AllCalls.PREFIX) ON UCCount.PREFIX = AllCalls.PREFIX
    WHERE (((AllCalls.PREFIX)=[Zones].[Zone] 
    And     (AllCalls.PREFIX)=[SCCount].[PREFIX] 
    And     (AllCalls.PREFIX)=[UCCount].[PREFIX]))
    GROUP BY Zones.Zone, 
                  Left([AllCalls.CALLTIME],13), 
                  AllCalls.PREFIX;
    BTW you have too many brackets there. It's MS Access generated code, isn't it? :-) Why don't you select AllCalls.PREFIX if you group by it?
    Last edited by madafaka; 11-13-05 at 15:28.

  7. #7
    Join Date
    Jan 2004
    Posts
    9

    Question

    Connected and notconnected are the count result from another query, and access wont let me run the query unless I have 'SCCount.Connected' and 'UCCount.NotConnected' in the grouping criteria ..

    Yes, MS Access keeps adding loads of brackets when i run the query and go bak 2 sql view

Posting Permissions

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