Home » SQL & PL/SQL » SQL & PL/SQL » Using SELECT to display various totals (12.2.0.2)
Using SELECT to display various totals [message #680494] Mon, 18 May 2020 18:36 Go to next message
johnw66
Messages: 3
Registered: March 2020
Junior Member
Dear sir/madam

I hooe you are all safe.

I wonder if you could please help me.

I need to create a PL/SQL select sratement to show various totals that do not available.

unfortunately, the fields we have available tou use are:

dept
work_centre
order_status
order_no
order_date
order_total

We need to report by dept, work_centre collating thr following totals

Totals fo last week week_tot,
tktal for month month_total
Tot for year-to-date ytd_total

Grouping by dept, work_centre

E.g.
Dept work_centre weektot month_tot ytd_tot
100 153261 10.00 250.00 5,575.23

What would be the most economical way to achieve this, I am currently using the same pl/zql statements with UNION

Thanking you in advance for your assistance.

John
Using SELECT to display various totals [message #680495 is a reply to message #680494] Mon, 18 May 2020 18:40 Go to previous messageGo to next message
johnw66
Messages: 3
Registered: March 2020
Junior Member
Dear sir/madam

I hooe you are all safe.

I wonder if you could please help me.

I need to create a PL/SQL select sratement to show various totals that do not available.

unfortunately, the fields we have available tou use are:

dept
work_centre
order_status
order_no
order_date
order_total

We need to report by dept, work_centre collating thr following totals

Totals fo last week week_tot,
tktal for month month_total
Tot for year-to-date ytd_total

Grouping by dept, work_centre

E.g.
Dept work_centre weektot month_tot ytd_tot
100 153261 10.00 250.00 5,575.23

What would be the most economical way to achieve this, I am currently using the same pl/zql statements with UNION

Thanking you in advance for your assistance.

John
Collating various totals inline [message #680496 is a reply to message #680494] Mon, 18 May 2020 19:34 Go to previous messageGo to next message
johnw66
Messages: 3
Registered: March 2020
Junior Member
Dear sir/madam

I hooe you are all safe.

I wonder if you could please help me.

I need to create a PL/SQL select sratement to show various totals that do not available.

unfortunately, the fields we have available tou use are:

dept
work_centre
order_status
order_no
order_date
order_total

We need to report by dept, work_centre collating thr following totals

Totals fo last week as week_tot,
total for month as month_total
Total for year-to-date as ytd_total

Grouping by dept, work_centre

E.g.
Dept work_centre week_total month_total ytd_total
100 153261 10.00 250.00 5,575.23

What would be the most economical way to achieve this, I am currently using the same pl/zql statements with UNION

Any pointers much appreciated.
Thanking you in advance for your assistance.

John
Re: Collating various totals inline [message #680498 is a reply to message #680496] Mon, 18 May 2020 20:59 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: Using SELECT to display various totals [message #680499 is a reply to message #680494] Tue, 19 May 2020 00:08 Go to previous message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.


Quote:
week_total month_total ytd_total

Week, month, year related to what? How do you define each term?


Quote:
I am currently using the same pl/zql statements with UNION

Show your code, this may help to understand what you want.
Don't forget to format and use [code] tags as explained in the link above.

Previous Topic: Transposing data in single row
Next Topic: ORA-21561: OID generation failed !!
Goto Forum:
  


Current Time: Fri Sep 18 03:51:38 CDT 2020