Postgresql select different values from the same column

Hopefully a simple question, but I am stuck

I need to select and display in separate columns the number of true and false counts from a single table column e.g.

request    Date            delivered
1              01/04/14     t
2              01/04/14     f
3              01/04/14     f
4              02/04/14     f
5              02/04/14     t
6              03/04/14     f
etc...

right now I can only do something like this:

SELECT
date_part(d, date) AS day,
count(request) AS requests,
delivered
FROM table
WHERE date > '2014-03-31 23:59:59'
and delivered = true
GROUP BY 1, delivered
ORDER BY 1

but I want to have a result that looks like this

date          delivered =t    delivered=f
01/04/14  1                      2
02/04/14  1                      1
03/04/14  0                      1

Can anyone help?
eezar21Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
only if the date field actually has date AND time, and you display only date part ...
see this article (though not targeted for postgresql):
http://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html

for your sql, you will need to extract/cast to only date, then the group by will work, for example date_trunc() function will do:
http://www.postgresql.org/docs/9.1/static/functions-datetime.html
select date_trunc('day', date)
, sum(case when delivered='t' then 1 else 0 end) t
, sum(case when delivered='f' then 1 else 0 end) f
from yourtable
group by date_trunc('day', date)
order by date_trunc('day', date) 

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this is the basic code:
select date
, sum(case when delivered='t' then 1 else 0 end) t
, sum(case when delivered='f' then 1 else 0 end) f
from yourtable
group by date
order by date

Open in new window

0
 
Pratima PharandeCommented:
Select Date ,

Sum ( Case when delivered = 't' then 1 else 0 end) as T,
Sum ( Case when delivered = 'f' then 1 else 0 end) as F


From table
Group by Date
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Pratima PharandeCommented:
Sorry AngelIII , have not refreshed the page
0
 
eezar21Author Commented:
Thanks guys but this gives me 2 different date lines for each date, rather than just one?
0
 
Pratima PharandeCommented:
Select Date::timestamp::date ,

Sum ( Case when delivered = 't' then 1 else 0 end) as T,
Sum ( Case when delivered = 'f' then 1 else 0 end) as F


From table
Group by Date::timestamp::date

refer
http://stackoverflow.com/questions/6133107/extract-date-yyyy-mm-dd-from-a-timestamp-in-postgresql
0
 
eezar21Author Commented:
Thanks for helping me through this - works perfectly and taught me some new query skills!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.