I'm trying to create a summary log by hour. Here's the query (somewhat
simplified):
select to_char(mtranti me,'mm-dd hh AM') as datetime,
count(*) as tot from memtran
group by datetime
order by datetime;
The problem is this produces the data in the following order:
datetime | tot
-------------+-----
04-08 01 PM | 14
04-08 02 PM | 15
04-08 03 PM | 23
04-08 07 AM | 8
04-08 08 AM | 54
04-08 09 AM | 30
04-08 10 AM | 11
04-08 11 AM | 10
04-08 11 PM | 7
04-08 12 PM | 10
What I'd really like is to get it in chronological order by hour:
04-08 07 AM | 8
04-08 08 AM | 54
04-08 09 AM | 30
04-08 10 AM | 11
04-08 11 AM | 10
04-08 12 PM | 10
04-08 01 PM | 14
04-08 02 PM | 15
04-08 03 PM | 23
04-08 11 PM | 7
I would prefer not to show the time of day in 24 hour format, but
there doesn't appear to be a way to order by something that
isn't in the select and group by clause and I don't want to display
the hour twice.
Putting the AM/PM before the HH (which looks a bit clumsy) almost works,
except that 12PM gets sorted to the bottom after 11PM.
Is there an easy way around this?
--
Mike Nolan
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org 4 7612
How about :
select to_char(mtranti me,'mm-dd hh AM') as datetime,
to_char(mtranti me,'AM') as sort_field,
count(*) as tot from memtran
group by sort_field, datetime
order by sort_field, datetime;
Then ignore the sort_field column?
Michael
"Mike Nolan" <no***@gw.tssi. com> wrote in message
news:20******** *************** **@gw.tssi.com. .. I'm trying to create a summary log by hour. Here's the query (somewhat simplified):
select to_char(mtranti me,'mm-dd hh AM') as datetime, count(*) as tot from memtran group by datetime order by datetime;
The problem is this produces the data in the following order:
datetime | tot -------------+----- 04-08 01 PM | 14 04-08 02 PM | 15 04-08 03 PM | 23 04-08 07 AM | 8 04-08 08 AM | 54 04-08 09 AM | 30 04-08 10 AM | 11 04-08 11 AM | 10 04-08 11 PM | 7 04-08 12 PM | 10
What I'd really like is to get it in chronological order by hour:
04-08 07 AM | 8 04-08 08 AM | 54 04-08 09 AM | 30 04-08 10 AM | 11 04-08 11 AM | 10 04-08 12 PM | 10 04-08 01 PM | 14 04-08 02 PM | 15 04-08 03 PM | 23 04-08 11 PM | 7
I would prefer not to show the time of day in 24 hour format, but there doesn't appear to be a way to order by something that isn't in the select and group by clause and I don't want to display the hour twice.
Putting the AM/PM before the HH (which looks a bit clumsy) almost works, except that 12PM gets sorted to the bottom after 11PM.
Is there an easy way around this? -- Mike Nolan
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org
Mike Nolan <no***@gw.tssi. com> writes: select to_char(mtranti me,'mm-dd hh AM') as datetime, count(*) as tot from memtran group by datetime order by datetime; The problem is this produces the data in the following order: ... What I'd really like is to get it in chronological order by hour:
You are grouping/ordering by the textual result of to_char(),
in which PM naturally follows AM. I think the behavior you
want would come from grouping/ordering by the underlying
timestamp column "mtrantime" .
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
> You are grouping/ordering by the textual result of to_char(), in which PM naturally follows AM. I think the behavior you want would come from grouping/ordering by the underlying timestamp column "mtrantime" .
Well, I need it grouped by hour, but that led me to the solution:
select to_char(date_tr unc('hour',mtra ntime),'mm-dd hh AM') as
datetime, count(*) as tot, from memtran
group by mtranoper, date_trunc('hou r',mtrantime)
order by mtranoper, date_trunc('hou r',mtrantime)
I knew there had to be a straight-forward solution. Thanks Tom.
--
Mike Nolan
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly
> How about : select to_char(mtranti me,'mm-dd hh AM') as datetime, to_char(mtranti me,'AM') as sort_field, count(*) as tot from memtran group by sort_field, datetime order by sort_field, datetime;
Then ignore the sort_field column?
I usually don't like to send managers reports with data labeled
'ignore this column'. :-)
With Tom's help, I found a solution.
--
Mike Nolan
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Bryan Harrington |
last post by:
Can you use Select top 40 * from ... and ORDER BY... in the same query?
I have the following 4 quearies that are.. for the most part, the same
except the order by clause, all return a differnt amount of records.
Thoughts?
select top 40 * from q_work_list where assigned_to = 10 order by batch_date,
remit_amt desc
40 records returned.
|
by: brett |
last post by:
Here is my SQL string:
"SELECT to_ordnum, to_orddate," _
& "(SELECT SUM((DDPROD.pr_stanmat * DDPROD.pr_prfact) *
(DOBOM2.b2_quant * DDORD.or_quant)) FROM DDPROD INNER JOIN DOBOM2 ON
DDPROD.pr_prodnum = DOBOM2.b2_prodnum INNER JOIN DDORD ON
DOBOM2.b2_orid = DDORD.or_id INNER JOIN DDTORD ON DDORD.OR_TOID =
DDTORD.TO_ID WHERE DOBOM2.b2_ordnum = ''order number here from result
of outer select) AS Total" _
& "FROM DDTORD WHERE to_trak2id IN...
|
by: war_wheelan |
last post by:
I am very new to Transact-SQL programming and don't have a programming
background and was hoping that someone could point me in the right
direction. I have a SELECT statement SELECT FIXID, COUNT(*) AS IOIs
and want to ORDER BY 'IOI's'. I have been combing through the BOL, but
I don't even know what topic/heading this would fall under.
USE INDII
SELECT FIXID, COUNT(*) AS IOIs
FROM .
GROUP BY FIXID
|
by: Tcs |
last post by:
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't
loaded it yet. I'm still using MS Access. And no, I don't believe this is an
Access question. (But who knows? I COULD be wrong... :) I've tried the
access group...twice...and all I get is "Access doesn't like ".", which I know,
or that my query names are too long, as there's a limit to the length of the SQL
statement(s). But this works when I don't try to...
|
by: Jean |
last post by:
Hello all,
I have the following data, that was queried and sorted to columns
PROBLEM_ID and then by STATUSDATE (ascending):
STATUS_ID STATUSDATE PROBLEM_ID
--------- ---------- ----------
10 12/04/2005 1234
40 15/05/2005 1234
| |
by: Jimmy |
last post by:
thanks to everyone that helped, unfortunately the code samples people gave me don't work. here is what i have so far:
<%
Dim oConn, oRS, randNum
Randomize()
randNum = (CInt(1000 * Rnd) + 1) * -1
Set oConn=Server.CreateObject("ADODB.Connection")
Set oRS=Server.CreateObject("ADODB.recordset")
oConn.Provider="Microsoft.Jet.OLEDB.4.0"
oConn.Open Server.MapPath("temp.mdb")
|
by: MP |
last post by:
vb6,ado,mdb,win2k
i pass the sql string to the .Execute method on the open connection to
Table_Name(const) db table
fwiw
(the connection opened via class wrapper:)
msConnString = "Data Source=" & msDbFilename
moConn.Properties("Persist Security Info") = False
moConn.ConnectionString = msConnString
moConn.CursorLocation = adUseClient
moConn.Mode = adModeReadWrite' or using default...same result
|
by: Simon Gare |
last post by:
Hi,
trying to retrieve postal codes from the db but only want the query to look
at the first 3 digits of the code tried using
(LEFT(dbo.booking_form.COLL_POST_CODE),3) but that doesn't work. I don't
want the query to count individual post codes but instead look at an area
found in the first 3 digits e.g. HA0 3TD is for a particular house but HA)
is for the area Harrow.
|
by: djflow |
last post by:
Hi!
II was wondering if you can help me with SQL query..
Below 7 separated select query works fine(only when they are retrieved separately)
But I want to combined them together and so that i can make report from my database on the one sheet rather than querying each select statement ( I will use MS Query from the Excel)
=========================================================
1)
select convert(varchar, create_date_time, 112) as Date,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |