pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL query to display like this
2+ messages / 2 participants
[nested] [flat]
* SQL query to display like this
@ 2007-08-24 10:42 RPK <rpk.general@gmail.com>
2007-08-24 11:36 ` Re: SQL query to display like this Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 2+ messages in thread
From: RPK @ 2007-08-24 10:42 UTC (permalink / raw)
To: pgsql-sql
I have a table with records like this:
Date EmpID ADCode ADAmount
-------------------------------------------------
01-Jul-07 101 GPF 150
01-Jul-07 102 GPF.ADV 100
01-Jul-07 103 GPF 200
01-jul-07 104 GPF 300
I want to show results like this using a single SQL query:
Date EmpID GPF GPF.ADV
-------------------------------------------------
01-Jul-07 101 150 0
01-Jul-07 102 0 100
01-Jul-07 103 200 0
01-Jul-07 104 300 0
I tried:
select PaySlipDate,EmpID,
case ADCode when 'GPF' then ADAmount else 0 end GPF,
case ADCode when 'GPF.ADV' then ADAmount else 0 end 'GPF.ADV'
from EmpSalaryRecord
It is showing like this:
Date EmpID GPF GPF.ADV
-----------------------------------------
01-Jul-07 101 0 0
01-Jul-07 101 150 0
01-Jul-07 102 0 100
01-Jul-07 103 0 0
01-Jul-07 103 1000 0
01-Jul-07 103 0 1000
It is showing multiple records of each employee for each date. First a
record with GPF and GPF.ADV both zero and then records with values. I want a
single record for each date and employee.
--
View this message in context: http://www.nabble.com/SQL-query-to-display-like-this-tf4322876.html#a12310093
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: SQL query to display like this
2007-08-24 10:42 SQL query to display like this RPK <rpk.general@gmail.com>
@ 2007-08-24 11:36 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 2+ messages in thread
From: Richard Huxton @ 2007-08-24 11:36 UTC (permalink / raw)
To: RPK <rpk.general@gmail.com>; +Cc: pgsql-sql
RPK wrote:
> select PaySlipDate,EmpID,
> case ADCode when 'GPF' then ADAmount else 0 end GPF,
> case ADCode when 'GPF.ADV' then ADAmount else 0 end 'GPF.ADV'
> from EmpSalaryRecord
>
> It is showing like this:
>
> Date EmpID GPF GPF.ADV
> -----------------------------------------
> 01-Jul-07 101 0 0
> 01-Jul-07 101 150 0
> 01-Jul-07 102 0 100
> 01-Jul-07 103 0 0
> 01-Jul-07 103 1000 0
> 01-Jul-07 103 0 1000
>
> It is showing multiple records of each employee for each date.
No it's not. It's showing 2 records for empid=101, 3 for empid=103 but
only 1 for empid=102. That says to me you have multiple rows in your
table for each (date,empid) combination.
If you want to eliminate them, either filter on AdCode or perhaps SUM()
your GPF/GPF.ADV colums.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-08-24 11:36 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-24 10:42 SQL query to display like this RPK <rpk.general@gmail.com>
2007-08-24 11:36 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox