SQL Query Help Please !

Hi all

I am using the following query for data to be displayed in crosstab :

SELECT closedate,status,
SUM (CASE WHEN empcode = 'NT-028' THEN 1 ELSE 0 END) AS NT028,
SUM (CASE WHEN empcode = 'NT-031' THEN 1 ELSE 0 END) AS NT031,
SUM (CASE WHEN empcode = 'NT-050' THEN 1 ELSE 0 END) AS NT050,
SUM (CASE WHEN empcode = 'NT-062' THEN 1 ELSE 0 END) AS NT062
FROM dbcleaning
WHERE status = 'Fixed'
AND empcode IN
('NT-028',
'NT-031',
'NT-050',
'NT-062')
GROUP BY closedate, status

Now i need a column in the same result of the query which shows the
difference between the two columns .

For Example :

the result shoud be

Closedate , status , NT028, NT031, NT050,NT062 , NT028-NT031

Please note the last column in bold, i need the difference .

Any help will be highly appreciated
--
View this message in context: http://old.nabble.com/SQL-Query-Help-Please-%21-tp29082529p29082529.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

GrGsM, 06.07.2010 09:06:
> Now i need a column in the same result of the query which shows the
> difference between the two columns .
>
> For Example :
>
> the result shoud be
>
> Closedate , status , NT028, NT031, NT050,NT062 , NT028-NT031
>
> Please note the last column in bold, i need the difference .
>

Already answered here:
http://www.dbforums.com/postgresql/1658135-sql-query-help-please.html

On Tue, Jul 06, 2010 at 12:06:06AM -0700, GrGsM wrote:
> SELECT closedate,status,
> SUM (CASE WHEN empcode = 'NT-028' THEN 1 ELSE 0 END) AS NT028,
> SUM (CASE WHEN empcode = 'NT-031' THEN 1 ELSE 0 END) AS NT031,
> SUM (CASE WHEN empcode = 'NT-050' THEN 1 ELSE 0 END) AS NT050,
> SUM (CASE WHEN empcode = 'NT-062' THEN 1 ELSE 0 END) AS NT062
> FROM dbcleaning
> WHERE status = 'Fixed'
> AND empcode IN
> ('NT-028',
> 'NT-031',
> 'NT-050',
> 'NT-062')
> GROUP BY closedate, status
>
> Now i need a column in the same result of the query which shows the
> difference between the two columns .
>
> For Example :
> the result shoud be
> Closedate , status , NT028, NT031, NT050,NT062 , NT028-NT031

Just put your code above in an "inner select", something like:

SELECT closedate, status, NT028-NT031 AS diff
FROM (
SELECT closedate,status, SUM(CASE WHEN ...
...
GROUP BY closedate, status) x;

--
Sam http://samason.me.uk/

On 7/6/2010 3:06 AM, GrGsM wrote:
>
> Hi all
>
> I am using the following query for data to be displayed in crosstab :
>
> SELECT closedate,status,
> SUM (CASE WHEN empcode = 'NT-028' THEN 1 ELSE 0 END) AS NT028,
> SUM (CASE WHEN empcode = 'NT-031' THEN 1 ELSE 0 END) AS NT031,
> SUM (CASE WHEN empcode = 'NT-050' THEN 1 ELSE 0 END) AS NT050,
> SUM (CASE WHEN empcode = 'NT-062' THEN 1 ELSE 0 END) AS NT062
> FROM dbcleaning
> WHERE status = 'Fixed'
> AND empcode IN
> ('NT-028',
> 'NT-031',
> 'NT-050',
> 'NT-062')
> GROUP BY closedate, status
>
> Now i need a column in the same result of the query which shows the
> difference between the two columns .

SUM (CASE WHEN empcode = 'NT-028' THEN 1 ELSE 0 END) -
SUM (CASE WHEN empcode = 'NT-031' THEN 1 ELSE 0 END) as NT028_NT031

Or if you prefer, define an outer select with your select in the FROM
clause; in the outer select, you can select each of the 4 columns plus
any difference you like.

--
Guy Rouillier

--
Guy Rouillier