Home » SQL & PL/SQL » SQL & PL/SQL » To calculate the no. of entries of transactions in database
To calculate the no. of entries of transactions in database [message #285276] Tue, 04 December 2007 03:28 Go to next message
yogeshyl
Messages: 50
Registered: October 2007
Location: Silvassa
Member
I need a query to find out the number of entries of a particular transaction.

If suppose i want to find the number of invoices entered, then what will be the query?

Yogesh
Re: To calculate the no. of entries of transactions in database [message #285280 is a reply to message #285276] Tue, 04 December 2007 03:33 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Select count(*) from abcd where .... . 


To be frank.. I am not sure what is your actual query

Thumbs Up
Rajuvan.
Re: To calculate the no. of entries of transactions in database [message #285287 is a reply to message #285280] Tue, 04 December 2007 03:39 Go to previous messageGo to next message
yogeshyl
Messages: 50
Registered: October 2007
Location: Silvassa
Member
I am very new to sql, and our ERP is in implementation phase. I was asked to give the status of entries completed & pending of Purchase, production, sales etc.

As there is no such report available at present in front end, where i can find out the status. I have to press page down key for that which is very time consuming.

Therefore, i would like to execute a query by which i can get the status of all pending & completed entries.

Re: To calculate the no. of entries of transactions in database [message #285292 is a reply to message #285280] Tue, 04 December 2007 03:44 Go to previous messageGo to next message
yogeshyl
Messages: 50
Registered: October 2007
Location: Silvassa
Member
I have written this query :-

SELECT 'PS', COUNT(*) FROM OT_PS_HEAD WHERE PSH_CR_DT = '23-OCT-2007'

But its giving me the result for only one date. How can i modify this query to find out the result from date.... to date...
Re: To calculate the no. of entries of transactions in database [message #285293 is a reply to message #285287] Tue, 04 December 2007 03:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How could we answer your question without knowing your model.
All we can do is to refer you to the SQL Reference

You want to count rows, use COUNT
You want to sum up values, use SUM
You want to aggregate data within groups, use GROUP BY
And so on.

Regards
Michel
Re: To calculate the no. of entries of transactions in database [message #285295 is a reply to message #285292] Tue, 04 December 2007 03:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
'23-OCT-2007' is not a date it is a string.
Only YOU, the human being, knows it is representing a date, Oracle does not know it and so you'll have problems.
For instance:
SQL> select to_date('23-OCT-2007') from dual;
select to_date('23-OCT-2007') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

Regards
Michel
Re: To calculate the no. of entries of transactions in database [message #285296 is a reply to message #285276] Tue, 04 December 2007 03:49 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Use BETWEEN keyword.

Kiran.
Re: To calculate the no. of entries of transactions in database [message #285298 is a reply to message #285293] Tue, 04 December 2007 03:50 Go to previous messageGo to next message
yogeshyl
Messages: 50
Registered: October 2007
Location: Silvassa
Member
I am using toad 8.5.1 and my ERP has d2k as front end & oracle back end.
Re: To calculate the no. of entries of transactions in database [message #285306 is a reply to message #285296] Tue, 04 December 2007 03:56 Go to previous messageGo to next message
yogeshyl
Messages: 50
Registered: October 2007
Location: Silvassa
Member
Thanks, got the solution for my problem.
Re: To calculate the no. of entries of transactions in database [message #285307 is a reply to message #285298] Tue, 04 December 2007 03:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This does not change anything to the SQL.

Regards
Michel
Re: To calculate the no. of entries of transactions in database [message #285308 is a reply to message #285276] Tue, 04 December 2007 03:58 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Can you show the query which you got the solution?

Kiran.
Re: To calculate the no. of entries of transactions in database [message #285309 is a reply to message #285307] Tue, 04 December 2007 03:59 Go to previous messageGo to next message
yogeshyl
Messages: 50
Registered: October 2007
Location: Silvassa
Member
I got the solution. I used BETWEEN keyword in my sql statement to find the entries from a particular date to till date.

Thanks,

Yogesh
Re: To calculate the no. of entries of transactions in database [message #285310 is a reply to message #285308] Tue, 04 December 2007 04:00 Go to previous messageGo to next message
yogeshyl
Messages: 50
Registered: October 2007
Location: Silvassa
Member
SELECT 'PS', COUNT(*) FROM OT_PS_HEAD WHERE PSH_CR_DT BETWEEN '01-OCT-2007' AND '04-DEC-2007'
UNION
SELECT 'PR', COUNT(*) FROM OT_PR_HEAD WHERE PRH_CR_DT BETWEEN '01-OCT-2007' AND '04-DEC-2007'
UNION
SELECT 'PWO', COUNT(*) FROM OT_PWO_HEAD WHERE PWH_CR_DT BETWEEN '01-OCT-2007' AND '04-DEC-2007'
UNION
SELECT 'INV', COUNT(*) FROM OT_INVOICE_HEAD WHERE INVH_CR_DT BETWEEN '01-OCT-2007' AND '04-DEC-2007'


This is the query which gave me the desired result.
Re: To calculate the no. of entries of transactions in database [message #285313 is a reply to message #285310] Tue, 04 December 2007 04:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use UNION ALL instead of UNION.
Use TO_DATE or you will likely have the errors I showed above.

Regards
Michel
Re: To calculate the no. of entries of transactions in database [message #285314 is a reply to message #285276] Tue, 04 December 2007 04:02 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Yogesh,
If the field is of datatype DATE then you should compare the
'23-OCT-2007' by converting to date. Something likethis,

 TO_DATE ('23102007', 'ddmmyyyy')


Kiran.
Re: To calculate the no. of entries of transactions in database [message #285350 is a reply to message #285314] Tue, 04 December 2007 05:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Since the original poster is not here to learn SQL, but to solve an ad-hoc one-time-only problem, he could care less if his query could fail in a database with another NLS-setting.
Pointing him to the fact that he might be in trouble with it in (completely) different circumstances is quite useless in my point of view.
If he were here to learn SQL, or as a developer, it would be a different story altogether..
Re: To calculate the no. of entries of transactions in database [message #285365 is a reply to message #285350] Tue, 04 December 2007 05:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Who knows in which environment his application will run?
Today here, tomorrow elsewhere.

Regards
Michel
Re: To calculate the no. of entries of transactions in database [message #285475 is a reply to message #285276] Tue, 04 December 2007 15:51 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Your query makes no sense.

SELECT 'PS', COUNT(*) FROM OT_PS_HEAD WHERE PSH_CR_DT BETWEEN '01-OCT-2007' AND '04-DEC-2007'
UNION
SELECT 'PR', COUNT(*) FROM OT_PR_HEAD WHERE PRH_CR_DT BETWEEN '01-OCT-2007' AND '04-DEC-2007'
UNION
SELECT 'PWO', COUNT(*) FROM OT_PWO_HEAD WHERE PWH_CR_DT BETWEEN '01-OCT-2007' AND '04-DEC-2007'
UNION
SELECT 'INV', COUNT(*) FROM OT_INVOICE_HEAD WHERE INVH_CR_DT BETWEEN '01-OCT-2007' AND '04-DEC-2007'


Will return the exact same counts for every query. All you are changing is the literal "PS,PR,PWO, and INV. If you want a count of all the invoice types during a range then do the following. I am guessing at the name of the invoice type column, please substitute the correct column name

SELECT invoice_type, COUNT(*) 
FROM OT_INVOICE_HEAD 
WHERE INVH_CR_DT BETWEEN to_date('01-OCT-2007','DD-MON-YYYY') AND TO_DATE('04-DEC-2007','DD-MON-YYYY')
GROUP BY INVOICE_TYPE
ORDER BY INVOICE_TYPE;
Re: To calculate the no. of entries of transactions in database [message #285494 is a reply to message #285276] Tue, 04 December 2007 22:20 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Then what about the tables OT_PS_HEAD ,OT_PR_HEAD ,OT_PWO_HEAD on which the OP is interested ? Smile

I guess that each table has its own Transaction types from OP's comments.

Thumbs Up
Rajuvan.
Re: To calculate the no. of entries of transactions in database [message #285543 is a reply to message #285475] Wed, 05 December 2007 00:32 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Bill, the tables are different in each query.

Regards
Michel
Previous Topic: Simple query involving count
Next Topic: Newbie error with IF Statement in SELECT
Goto Forum:
  


Current Time: Thu Dec 08 10:16:04 CST 2016

Total time taken to generate the page: 0.13575 seconds