Обсуждение: 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