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

Group by with criteria

I have a Problem with my Select statement!
I want to Select every Record, where ChNr fits the pattern the User
chooses. And sum up the different Source Columns.
So far this one works out fine, but I want to have the sum over ALL ChNr
that fit the pattern given, not a result for every ChNr.
If that is of any interest for you, ChNr always looks the same: three
numbers-three numbers e.g. 481-102, 581-235.
SELECT DISTINCT a.ChNr, Sum(a.St?ckzahl) AS St?ck, Sum(a.[BHL 25?m]) AS
BHL25, Sum(a.[BHL 32?m]) AS BHL32, Sum(a.[BMRH 25?m]) AS BMRH25, Sum(a.
[BMRH 32?m]) AS BMRH32, ((1000000/(St?ck*120))*(BHL25+BMRH25)) AS ppm25, (
(1000000/(St?ck*120))*(BHL32+BMRH32)) AS ppm32
FROM tbAuswert a
WHERE (ChNr LIKE "491-%" OR ChNr LIKE "482-%")
GROUP BY ChNr;
With this Statement I get one Result for every single ChNr. What I want is
ONE result for all ChNr that look like 491-... .
I thought I would have to make a criteria in the Group by part, but I just
can't make it work! I already tried to put another LIKE thing into Group
by, but it just won't do.
Unfortunately the DataBase I am working with is Access.
I am working with Visual Studio and C#.
I hope I made myself clear. It is really not so easy to explain my problem.
Thanks Julia
Message posted via http://www.webservertalk.comIf I understand you correctly, this should do it:
SELECT LEFT(a.ChNr,3) as [NAME], Sum(a.St?ckzahl) AS St?ck, Sum(a.[BHL
25?m]) AS
BHL25, Sum(a.[BHL 32?m]) AS BHL32, Sum(a.[BMRH 25?m]) AS BMRH25, Sum(a.
[BMRH 32?m]) AS BMRH32, ((1000000/(St?ck*120))*(BHL25+BMRH25)) AS ppm25, (
(1000000/(St?ck*120))*(BHL32+BMRH32)) AS ppm32
FROM tbAuswert a
WHERE (ChNr LIKE "491-%" OR ChNr LIKE "482-%")
GROUP BY LEFT(ChNr);
-oj
"Julia H?rtfelder via webservertalk.com" <forum@.webservertalk.com> wrote in
message news:2050d4f1fa8c4293b71ff00dc45057d1@.SQ
webservertalk.com...
>I have a Problem with my Select statement!
> I want to Select every Record, where ChNr fits the pattern the User
> chooses. And sum up the different Source Columns.
> So far this one works out fine, but I want to have the sum over ALL ChNr
> that fit the pattern given, not a result for every ChNr.
> If that is of any interest for you, ChNr always looks the same: three
> numbers-three numbers e.g. 481-102, 581-235.
> SELECT DISTINCT a.ChNr, Sum(a.St?ckzahl) AS St?ck, Sum(a.[BHL 25?m]) AS
> BHL25, Sum(a.[BHL 32?m]) AS BHL32, Sum(a.[BMRH 25?m]) AS BMRH25, Sum(a.
> [BMRH 32?m]) AS BMRH32, ((1000000/(St?ck*120))*(BHL25+BMRH25)) AS ppm25, (
> (1000000/(St?ck*120))*(BHL32+BMRH32)) AS ppm32
> FROM tbAuswert a
> WHERE (ChNr LIKE "491-%" OR ChNr LIKE "482-%")
> GROUP BY ChNr;
>
> With this Statement I get one Result for every single ChNr. What I want is
> ONE result for all ChNr that look like 491-... .
> I thought I would have to make a criteria in the Group by part, but I just
> can't make it work! I already tried to put another LIKE thing into Group
> by, but it just won't do.
> Unfortunately the DataBase I am working with is Access.
> I am working with Visual Studio and C#.
> I hope I made myself clear. It is really not so easy to explain my
> problem.
> Thanks Julia
> --
> Message posted via http://www.webservertalk.com|||The Idea is fine! This is exactly what I need!
I tried it and first I got the message of a missing parameter, so I looked
up left() and now my Statement looks like this:
SELECT DISTINCT a.ChNr, Sum(a.St?ckzahl) AS St?ck, Sum(a.[BHL 25?m]) AS
BHL25, Sum(a.[BHL 32?m]) AS BHL32, Sum(a.[BMRH 25?m]) AS BMRH25, Sum(a.
[BMRH 32?m]) AS BMRH32, ((1000000/(St?ck*120))*(BHL25+BMRH25)) AS ppm25, (
(1000000/(St?ck*120))*(BHL32+BMRH32)) AS ppm32
FROM tbAuswert a
WHERE (ChNr LIKE "491-%" OR ChNr LIKE "482-%")
GROUP BY LEFT (a.Auswert, 4);
But now I get the following Message:
"You tried to execute a query that does not include the specified
expression 'ChNr' as part of an aggregate function"
So, what could might be the Problem now?
Message posted via http://www.webservertalk.com|||You cannot be grouping by left(col,4) and not including it as part of your
select.
So, change your SELECT DISTINCT a.ChNr to SELECT left(a.ChNr,4).
DISTINCT is redundant here when you do grouping.
-oj
"Julia H?rtfelder via webservertalk.com" <forum@.webservertalk.com> wrote in
message news:b7da74bb92c645fb868096d31d45aa70@.SQ
webservertalk.com...
> The Idea is fine! This is exactly what I need!
> I tried it and first I got the message of a missing parameter, so I looked
> up left() and now my Statement looks like this:
> SELECT DISTINCT a.ChNr, Sum(a.St?ckzahl) AS St?ck, Sum(a.[BHL 25?m]) AS
> BHL25, Sum(a.[BHL 32?m]) AS BHL32, Sum(a.[BMRH 25?m]) AS BMRH25, Sum(a.
> [BMRH 32?m]) AS BMRH32, ((1000000/(St?ck*120))*(BHL25+BMRH25)) AS ppm25, (
> (1000000/(St?ck*120))*(BHL32+BMRH32)) AS ppm32
> FROM tbAuswert a
> WHERE (ChNr LIKE "491-%" OR ChNr LIKE "482-%")
> GROUP BY LEFT (a.Auswert, 4);
> But now I get the following Message:
> "You tried to execute a query that does not include the specified
> expression 'ChNr' as part of an aggregate function"
> So, what could might be the Problem now?
> --
> Message posted via http://www.webservertalk.com|||How blind can one woman be'
Thank you so much!!! It Works!
You are a genius! ;-)
Just another small question concerning the DISTINCT.
It might come up, that I have a record double except from the Key (which
are consecutive numbers ), doesen't DISTINCT filter the double out before
summing up?
Message posted via http://www.webservertalk.com|||;-) you're welcome.
See if this example helps:
create table #tmp(i sysname, j int)
insert #tmp select '123-456',1
insert #tmp select '123-456',1
insert #tmp select '123-456',2
insert #tmp select '123-456',2
insert #tmp select '123-456',2
insert #tmp select '456-456',2
insert #tmp select '456-456',3
insert #tmp select '789-456',5
insert #tmp select '789-456',5
insert #tmp select '789-456',1
go
--only distinct j
select left(i,4) i, sum(distinct j) s
from #tmp
where i like '123-%' or i like '789-%'
group by left(i,4)
--regular
select left(i,4) i, sum(j) s
from #tmp
where i like '123-%' or i like '789-%'
group by left(i,4)
--redundant distinct
select distinct left(i,4) i, sum(j) s
from #tmp
where i like '123-%' or i like '789-%'
group by left(i,4)
go
drop table #tmp
go
-oj
"Julia H?rtfelder via webservertalk.com" <forum@.webservertalk.com> wrote in
message news:e2ed74fec66e45c7b222a42b4f653e82@.SQ
webservertalk.com...
> How blind can one woman be'
> Thank you so much!!! It Works!
> You are a genius! ;-)
> Just another small question concerning the DISTINCT.
> It might come up, that I have a record double except from the Key (which
> are consecutive numbers ), doesen't DISTINCT filter the double out before
> summing up?
> --
> Message posted via http://www.webservertalk.com