NEED TRICKY SELECT STATEMENT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

NEED TRICKY SELECT STATEMENT

Author

Topic

jazzyb
Starting Member

20 Posts

Posted - 2011-03-07 : 09:52:23

Respected Friends Hi

I have one table named as 'payroll' with the columns employee_id,employee_name,employee_designation,basicpay,allowances,month,year.

This table data as follwoing:

employee_id , employee_name , employee_designation , basicpay, allowances, month, year

1 , SAM , MANAGER , 5000 , 1000 , JANUARY , 2011
1 , SAM , MANAGER , 6000 , 1000 , FEBRUARY , 2011
1 , SAM , MANAGER , 6500 , 1000, MARCH , 2011
2 , GEORGE , EXECUTIVE , 3000 , 500 , JANUARY , 2011
2 , GEORGE , EXECUTIVE , 3500 , 500 , FEBRUARY, 2011
2 , GEORGE , EXECUTIVE , 3500 , 500 , MARCH ,2011

How should I write the SQL Select statement to get (when some columns need SUM()) the following result(OUTPUT):

employee_id = 1
employee_name = SAM
employee_designation = MANAGER

TOTAL_basicpay = 17500
TOTAL_allowances = 3000

year = 2011

Waiting for your kind suggestions

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-07 : 10:27:21

Take a look at this MSDN page - it has examples http://msdn.microsoft.com/en-us/library/ms187810.aspx. In most cases, you will need to use a group by clause, and the columns in the select list that are not aggregated should be in the group by list. So in your case it would be something like this:

select
   employee_id,
   employee_name,
   employee_designation,
   sum(basicpay) as Total_basicpay,
   sum(allowances) as Total_allowances
from
   payroll
where
   employee_id = 1
group by
   employee_id,
   employee_name,
   employee_designation

jazzyb
Starting Member

20 Posts

Posted - 2011-03-07 : 11:26:05

Friend Sunitabeck Thanks for your suggestion

To refine my question : I want group by employee_id only because there can be minor differences in the spell of name and designation but the employee_id will be assigned by system in employee table. I mean only the employee_id will be unique ID for every employee.

thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-07 : 12:27:52

You have to either do an aggregation, or you must do group by.

For example, let us say I was an employee at your company. In some places, my name is spelled Sunita. In other places, it might be spelled as Sunitha. (Tell me who keyed it into your system with an "h", I want to attack them!! :--). Now if you were to report my salary aggregated over a few months, you have to pick one spelling or the other. (Or you would need to more complicated rules for reporting the name, for example, "the employee formerly known as Sunitha").

So you could do this:

select
   employee_id,
   min(employee_name) as employee_name,
   .....

If you did that, you wouldn't need to group by the employee name. But, the name that you would get in the result set would be the name that comes first in alphabetical sort order. So in my case, it would pick up Sunita rather than Sunitha. But that may not always be what you want.

As an aside, if you inherited this database, you may not have much choice, but if you are still designing the database and tables, you may want to change the design a little bit. Create separate tables, one for Employee and one for Salary. If you normalize the design in this manner, that will avoid the issue you are facing now. And, you would be able to enhance the design in the future to keep track of name changes etc.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-03-07 : 18:05:27

You have several problems. Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help. here.

The first thing you would notice is that you did not normalize this table. Personnel, job assignments and payroll are different entities

CREATE TABLE Payroll
(emp_id INTEGER NOT NULL
REFERENCES Personnel(emp_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
pay_date DATE,
base_pay_amt DECIMAL (8,2) NOT NULL,
allowance_amt DECIMAL (8,2) NOT NULL);

From this we can construct a monthly VIEW that gives us what you posted.

>> How should I write the SQL Select statement to get (when some columns need SUM()) the following result(OUTPUT): <<

This is your real problem; you do know what a query is or how RDBMS works. Your narrative was a report, with display formatting. That is always done in the front and never in the database.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

jazzyb
Starting Member

20 Posts

Posted - 2011-03-07 : 19:47:28

Sunita and Jecklo,

Thanks for your suggestions and I will work on these suggestions. Sunita you have captured my actual problem and I will be back if still problemm persist.

Dear Jecklo, Your suggestions are valuable, I am new to programming and making a small application for my office. But I will work on basic principles of SQL.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources