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 :)

ThksI 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.|||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 .."

:S|||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: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>|||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|||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?

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?|||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 :Ssql