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:

[code=oracle]

SELECT trunc(edate),su m(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)

[/code]

[QVeen72]

Hi,

Try This:

[code=oracle]
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)
[/code]

Regards
Veena