topic table db query in Operating System - HP-UX

table db query

Ferdi Castro — Sun, 14 May 2006 04:50:06 GMT

I have a bank table that looks like below
Denom Serial created
$10 333111 10-MAY-06
$10 111888 09-APR-06
$5 555666 01-JAN-89
.
.
.
.
$20 666877 09-SEP-98

I selected number of denominations created
"select distinct(denom),count(*) from bank which is ok.
Now I wanted to select denom, the number of denom created yesterday, & total denoms created as of yesterday in single output. Can someone help me here . thanks in advance...

Re: table db query

Brian Crabtree — Sun, 14 May 2006 11:25:41 GMT

Hmmmm. I think I understand what you are asking for, so try something like this.

select denom, count(*) from back where created > trunc(sysdate)-1 and created < trunc(sysdate);

That should get you only yesterday (by that, I mean 12am to 12am). Also, I'm assuming Oracle in this case.

Brian

Re: table db query

Indira Aramandla — Sun, 14 May 2006 21:17:53 GMT

Hi Ferdi,

As Brian mentioned you can use the sysdate to get the the previous / next dayas records.

For eg:- This query will give you records
$5 555666 ---- which are before January 2006.

SQL> SELECT distinct denom, count(*()
FROM bank
WHERE created < '01-JAN-2006'
GROUP BY DEMON;

For yesterday use sysdate -1

To manupulate the datetime use the to_char(created,'DD-MON-YYYY:HH:MI:SS')

Indira A