spilit one column into two columns

[arulkumara]

Hi,

My table
******************
edate entry
******************
01-02-2008 in
01-02-2008 in
01-02-2008 out
01-02-2008 out
01-02-2008 in
01-02-2008 in


i need count of entry column for in and out values

i need following output:
**************************

edate -------- COUNT(IN) -------- COUNT(OUT)

01-02-2008 -------- 4 ----------- 2


I tried case statement.But i didn't got exact o/p

[amitpatel66]

Please post what you tried so far?

[arulkumara]

Hi I got following output

1 SELECT sum(CASE WHEN entry='in' THEN 1 ELSE 0 END ) as entryin,
2 sum(CASE WHEN entry='out' THEN 1 ELSE 0 END ) as entryout,edate
3 FROM app
4* GROUP BY edate
SQL> /

ENTRYIN | ENTRYOUT | EDATE
---------- | ---------- | ----------
1 | 0 | 02-09-2008
1 | 0 | 02-09-2008
1 | 0 | 02-09-2008
1 | 0 | 02-09-2008
0 | 1 | 02-09-2008
0 | 3 | 02-09-2008
0 | 1 | 02-09-2008

7 rows selected.


but i need this output:
*****************************

edate ------- ENTRYIN ------- ENTRYOUT

01-02-2008 ---------------- 4 --------------- 5

[amitpatel66]

Hi I got following output

1 SELECT sum(CASE WHEN entry='in' THEN 1 ELSE 0 END ) as entryin,
2 sum(CASE WHEN entry='out' THEN 1 ELSE 0 END ) as entryout,edate
3 FROM app
4* GROUP BY edate
SQL> /

ENTRYIN | ENTRYOUT | EDATE
---------- | ---------- | ----------
1 | 0 | 02-09-2008
1 | 0 | 02-09-2008
1 | 0 | 02-09-2008
1 | 0 | 02-09-2008
0 | 1 | 02-09-2008
0 | 3 | 02-09-2008
0 | 1 | 02-09-2008

7 rows selected.


but i need this output:
*****************************

edate ------- ENTRYIN ------- ENTRYOUT

01-02-2008 ---------------- 4 --------------- 5

Is your date column having a time stamp also?? Try this:

 
SELECT trunc(edate),sum(CASE WHEN entry='in' THEN 1 ELSE 0 END ) as entryin,
  sum(CASE WHEN entry='out' THEN 1 ELSE 0 END ) as entryout,edate
  FROM app
 GROUP BY TRUNC(edate)
 
 

[QVeen72]

Hi,

Try This:

SELECT substr(edate, 1, 10), 
    SUM(CASE WHEN edate LIKE '%in' THEN 1 ELSE 0 END) 
    AS entryin, 
    SUM(CASE WHEN edate LIKE '%out' THEN 1 ELSE 0 END) 
    AS entryout
FROM MyTable
GROUP BY substr(edate, 1, 10)
 

Regards
Veena