select case return rows in one row (how to group by three column) 
Author Message
 select case return rows in one row (how to group by three column)

I got a question.
how can I get  this three column in one row ?
OTOMATIK_TOPLAM,MANUEL_TOPLAM,NOBET_TOPLAM

it returns like this:
.
.
BEYHAN,1,NULL,1,NULL
BEYHAN,3,3,NULL,NULL
DNYA,4,NULL,4,NULL
DNYA,2,2,NULL,NULL
.
.

but I want this result like :
.
.
BEYHAN,4,3,1,NULL
DNYA,6,2,4,NULL
.
.
Where am I doing wrong ?

the SQL Statement is following:
----------------------------------------------------------------------------
--------
SELECT
E.ECZADI,
(COUNT(distinct RECETE_NO)) AS RECETE_MIKTARI,
(
SELECT CASE H.HAREKET_SEKLI WHEN 'OTOMATIK' THEN
COUNT(distinct RECETE_NO) ELSE null END
) AS OTOMATIK_TOPLAM
,
(
SELECT CASE H.HAREKET_SEKLI WHEN 'MANUEL' THEN
COUNT(distinct RECETE_NO) ELSE null END
) AS MANUEL_TOPLAM
,
(
SELECT CASE H.HAREKET_SEKLI WHEN 'NOBET' THEN
COUNT(distinct RECETE_NO) ELSE null END
) AS NOBET_TOPLAM
FROM PRM_ECZANELER E

LEFT OUTER JOIN HAREKET H ON (E.ECZADI=H.ECZANE_ADI)
WHERE E.ECZADI=:ECZADI
GROUP BY E.ECZADI,H.HAREKET_SEKLI

ORDER BY E.ECZADI
----------------------------------------------------------------------------
----------thanks for replies before:Yusuf Tulgar (DBAAdmin Havelsan TR)



Sun, 11 Jun 2006 19:25:40 GMT
 select case return rows in one row (how to group by three column)

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.  My guess is that you meant  soemthing like this:

SELECT E1.eczadi,
       SUM(recete_no) S recete_miktari,
       SUM(rece_no) AS otomatik_toplam,
       SUM(recete_no) AS manuel_toplam,
       SUM(recete_no) AS nobet_toplam
  FROM Prm_Eczaneler AS E1
       LEFT OUTER JOIN
       Hareket AS H1
       ON E1.eczadi = H1.eczane_adi
WHERE E1.eczadi = :eczadi
GROUP BY E1.eczadi, H1.hareket_sekli;

--CELKO--

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Mon, 12 Jun 2006 20:31:52 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. SELECT multiple rows back as one row with many columns

2. select * from dual in MOUNTED database returns 4 columns (one row)

3. returning one row before and one row after

4. column in multiple rows to multiple columns in one row

5. question regarding select (multiple rows select into one result row)

6. column in multiple rows to multiple columns in one row

7. concatenate varchar field from multiple rows into one row as a group

8. sql question for single row query returns more than one row

9. Dreaded ORA-01427: single-row subquery returns more than one row error

10. Newbie: single-row subquery returns more than one row

11. ORA-01427: single-row subquery returns more than one row

12. Grouping multiple rows into 1 row during a select into


 
Powered by phpBB® Forum Software