need help in sql query

Question

0.00/5 (No votes)

See more:

sir, i have i sql query:

SQL

select (Case when P_SalaryDueTotal.M_Code='Jan/2014' then sum(P_SalaryDueTotal.NetSalary) end) as 'Jan/2014',
        (Case when P_SalaryDueTotal.M_Code='Feb/2014' then sum(P_SalaryDueTotal.NetSalary) end) as 'Feb/2014',
        (Case when P_SalaryDueTotal.M_Code='Mar/2014' then sum(P_SalaryDueTotal.NetSalary) end) as 'Mar/2014'
FROM P_SalaryDueTotal
WHERE     (P_SalaryDueTotal.DepartmentID = 1359) AND (P_SalaryDueTotal.M_Code IN ('Jan/2014','Feb/2014','Mar/2014'))
GROUP BY P_SalaryDueTotal.M_Code

it gives output like :-

SQL

Jan/2014  Feb/2014  Mar/2014
NULL    198382.92   NULL
222316.00   NULL    NULL
NULL    NULL    247137.72

but i need out like:-

SQL

Jan/2014  Feb/2014  Mar/2014
222316.00   198382.92   247137.72

how can i do it..

Posted 7-Dec-14 20:47pm

TCS54321

Add a Solution

Comments

Kornfeld Eliyahu Peter 8-Dec-14 2:54am

Look for aggregate function SUM...

3 solutions

Solution 1

hi,

Try this

select sum(isnull( (Case when P_SalaryDueTotal.M_Code='Jan/2014' then sum(P_SalaryDueTotal.NetSalary) end),0)) as 'Jan/2014',
sum(isnull( (Case when P_SalaryDueTotal.M_Code='Feb/2014' then sum(P_SalaryDueTotal.NetSalary) end),0)) as 'Feb/2014',
sum(isnull( (Case when P_SalaryDueTotal.M_Code='Mar/2014' then sum(P_SalaryDueTotal.NetSalary) end),0)) as 'Mar/2014'
FROM P_SalaryDueTotal WHERE (P_SalaryDueTotal.DepartmentID = 1359) AND (P_SalaryDueTotal.M_Code IN ('Jan/2014','Feb/2014','Mar/2014'))
GROUP BY P_SalaryDueTotal.M_Code

Posted 7-Dec-14 20:54pm

mudgilsks

Comments

TCS54321 8-Dec-14 2:58am

Error:Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Solution 2

You can use pivot query to display your result :Check this query

SQL

DECLARE @MyColumns AS NVARCHAR(MAX),
    @SQLquery  AS NVARCHAR(MAX)
-- here first we get all the ItemName which should be display in Columns we use this in our necxt pivot query
select @MyColumns = STUFF((SELECT ',' + QUOTENAME(M_Code) 
                    FROM P_SalaryDueTotal
                    GROUP BY M_Code
                    ORDER BY M_Code
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
-- here we use the above all Item name to disoplay its price as column and row display
set @SQLquery = N'SELECT DepartmentID,' + @MyColumns + N' from 
             (
                 SELECT   
                 DepartmentID    
        NetSalary as TotAmount 
    FROM P_SalaryDueTotal
    WHERE     (P_SalaryDueTotal.DepartmentID = 1359) AND (P_SalaryDueTotal.M_Code IN (''Jan/2014'',''Feb/2014'',''Mar/2014''))
            ) x
            pivot 
            (
                 SUM(TotAmount)
                for M_Code in (' + @MyColumns + N')
            ) p '
exec sp_executesql @SQLquery;

check my article related to pivot query.

Basic SQL Queries for Beginners[^]

Posted 7-Dec-14 21:03pm

syed shanu

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

mudgilsks · Accepted Answer · 2014-12-07T21:05:00

hi

try this

SQL

select (Case when P_SalaryDueTotal.M_Code='Jan/2014' then sum(P_SalaryDueTotal.NetSalary) end) as 'Jan/2014',
        (Case when P_SalaryDueTotal.M_Code='Feb/2014' then sum(P_SalaryDueTotal.NetSalary) end) as 'Feb/2014',
        (Case when P_SalaryDueTotal.M_Code='Mar/2014' then sum(P_SalaryDueTotal.NetSalary) end) as 'Mar/2014' into #temp
FROM P_SalaryDueTotal
WHERE     (P_SalaryDueTotal.DepartmentID = 1359) AND (P_SalaryDueTotal.M_Code IN ('Jan/2014','Feb/2014','Mar/2014'))
GROUP BY P_SalaryDueTotal.M_Code

select sum(isnull([Jan/2014],0)) [Jan/2014],
sum(isnull([Feb/2014,0)) [Feb/2014],
sum(isnull([Mar/2014],0)) [Mar/2014] from #temp

drop table #temp