HI Everybody,
I have created one table called Employee.this table cantains four columns
ID,NAME,DEPTART MENT,SALARY.
Now i want out put in below mentioned format.
Emp.ID Emp.Name Dept. Salary Total Salary by Dept
1 X1 Software 5000
2 X2 Software 7000
12000
3 X3 Testing 8000
4 X4 Testing 4000
5 X5 Testing 3000
15000
6 X6 HR 2000
7 X7 HR 1000
3000
Can anyone please help to get output in this format?
Thanks in advance.
amit
3 1374
HI Everybody,
I have created one table called Employee.this table cantains four columns
ID,NAME,DEPTART MENT,SALARY.
Now i want out put in below mentioned format.
Emp.ID Emp.Name Dept. Salary Total Salary by Dept
1 X1 Software 5000
2 X2 Software 7000
12000
3 X3 Testing 8000
4 X4 Testing 4000
5 X5 Testing 3000
15000
6 X6 HR 2000
7 X7 HR 1000
3000
Can anyone please help to get output in this format?
Thanks in advance.
amit
-
-
SQL> ed
-
Wrote file afiedt.buf
-
-
1 select t.id,SUM(salary) from
-
2 (select 1 AS ID, 'X1' AS NAME, 'SW' AS DEPT, 1000 AS SALARY from dual
-
3 UNION
-
4 select 2 AS ID, 'X2' AS NAME, 'SW' AS DEPT, 1000 AS SALARY from dual
-
5 UNION
-
6 select 3 AS ID, 'X3' AS NAME, 'S' AS DEPT, 1000 AS SALARY from dual
-
7 UNION
-
8 select 4 AS ID, 'X4' AS NAME, 'S' AS DEPT, 3000 AS SALARY from dual) t
-
9* GROUP BY ROLLUP(dept,t.id)
-
SQL> /
-
-
ID SUM(SALARY)
-
---------- -----------
-
3 1000
-
4 3000
-
4000
-
1 1000
-
2 1000
-
2000
-
6000
-
-
7 rows selected.
-
-
SQL>
-
-
-
-
SQL> ed
-
Wrote file afiedt.buf
-
-
1 select t.id,SUM(salary) from
-
2 (select 1 AS ID, 'X1' AS NAME, 'SW' AS DEPT, 1000 AS SALARY from dual
-
3 UNION
-
4 select 2 AS ID, 'X2' AS NAME, 'SW' AS DEPT, 1000 AS SALARY from dual
-
5 UNION
-
6 select 3 AS ID, 'X3' AS NAME, 'S' AS DEPT, 1000 AS SALARY from dual
-
7 UNION
-
8 select 4 AS ID, 'X4' AS NAME, 'S' AS DEPT, 3000 AS SALARY from dual) t
-
9* GROUP BY ROLLUP(dept,t.id)
-
SQL> /
-
-
ID SUM(SALARY)
-
---------- -----------
-
3 1000
-
4 3000
-
4000
-
1 1000
-
2 1000
-
2000
-
6000
-
-
7 rows selected.
-
-
SQL>
-
-
Thanks for your reply,
but, i already got output in this format.
What i want is,"tolal salary by department" should be displayed in separate column. That i have mentioned below.
ID SUM(SALARY) Total Salary by Dept.
---------- ----------- ----------------------------
3 1000
4 3000
------------------------------------4000
1 1000
2 1000
------------------------------------2000
------------------------------------6000
Thanks in advance,
Amit Parmar
Try this: -
SQL> ed
-
Wrote file afiedt.buf
-
-
1 select empid,dept,salary ss,NULL from data1
-
2 UNION ALL
-
3 SELECT NULL,dept,NULL, sum(salary) from data1 group by dept
-
4 UNION ALL
-
5 SELECT NULL,NULL,NULL,SUM(SALARY) FROM data1
-
6* order by dept
-
SQL> /
-
-
EMPID DEPT SS NULL
-
---------- -------------------- ---------- ----------
-
1 SW 10000
-
2 SW 1000
-
5 SW 100
-
SW 11100
-
3 WWS 10000
-
4 WWS 1000
-
WWS 11000
-
22100
-
-
8 rows selected.
-
-
SQL>
-
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Craig Thomson |
last post by:
Do people generally try and output nicely formatted html from their
scripts?
I have been trying to, with variable indenting for tables etc. It
makes it easier to see the html and perhaps debug it, but it can make
ugly looking scripts.
What's the general consensus?
Craig
|
by: Mark |
last post by:
How do I output formatted numerical data to an external (text) file. At
a minimum I want to specify the number of decimal places to print.
Ideally I'd like the type of control the fortran FORMAT statement
offers. I'm sure this is easy, but I can't seem to find the answer!
I want an output file to look like this:
0.1 0.8575
0.2 12.0900
0.4 345.0000
|
by: Matthew Thorley |
last post by:
Greetings, perhaps someone can explain this. I get to different styles
of formatting for xmla and xmlb when I do the following:
from elementtree import ElementTree as et
xmla = et.ElementTree('some_file.xml')
xmlb = et.Element('parent')
et.SubElement(xmlb, 'child1')
et.SubElement(xmlb, 'child2')
|
by: Joe C |
last post by:
I've written a console application and would like to isolate all screen
output so that it will be easier to migrate the code to a GUI-type platform
without modification to the base code. As a first step, I've created a
variable:
bool useConsole(true);
then I trap all output like:
if(useConsole){
cout << text;
}
|
by: Jason Heyes |
last post by:
I am starting to worry about the performance of formatted read/write
operations on data-redundant objects in my program.What can I do to improve
this performance should it become an issue?
Thanks.
| |
by: Craig Petrie |
last post by:
Hi,
I have a large table in Word 2003 that has formatted text in the cells and
wish to read and convert a cells formatted contents to html output via
vb.net code. The formatting contains the following: bold text, italic text,
superscript & subscript text, bullet points. The output html is then saved
in an XML file as a CDATA section for later use in the application when
formatted output is required.
I have looked up many sites on the...
|
by: rufi |
last post by:
hi group,
I am new to C and using gcc compiler in Linux.
I am having the following problems when using the printf() funtion to have the formatted output.
Here r some things which i m trying :
The range of short int(which is signed by default) is -32768 to + 32767.
|
by: Jojo |
last post by:
Hi all,
I was wondering how I can perform formatted output with C++ strings. For
example, suppose I have in plain C:
sprintf(C_string, "%5.2f %6d");
How can I do such a thing with C++ strings? Using a string stream and
the << operator does not give me the formatting control like sprintf. Or
did I miss something.... :-|
|
by: keith |
last post by:
I've been beating my head against this for a little while, so perhaps
someone can help me out here? The code below should output exactly as
follows (the hex data lines up in a fixed font):
DATA 0123456789abcdef0123456789abcdef
0123456789abcdef0123456789abcdef
and the code does indeed do that when I use the fprintf() call. When
I comment that line out and use the iostream line, I get gibberish. I
guess I'm just not 'getting' C++...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
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: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |