psql command - how do you combine 2 searches into one command line

edomingox · 05-12-2011, 06:22 AM

I'm trying to figure out how to run the two following commands simultaneously:

Code:

1.    psql -U user myDB -c "select city, count(1) as total_people
 from list where time_up > '2011-05-12 05:00:00' and time_up < 
 '2011-05-12 06:00:00' group by city order by city;"

2.    psql -U user myDB -c "select city, count(1) as 
total_people_with_no_car from list where car is null and time_up > 
'2011-05-12 05:00:00' and time_up <  '2011-05-12 06:00:00' group by 
city order by city;"

Then I hope to show the following:

city    |  total_people_with_no_car  | total_people
----------------------------------------------------
Miami   |                 3232       |     35345
Denver  |                 4234       |     83883
Chicago |                 4445       |     82834

numbers don't mean anything, but you get the idea. Is it even possible to do this?

sunnydrake · 05-12-2011, 08:28 AM

something like this but there is other ways.
mysql> SELECT a.*,b.* FROM (SELECT id from jos_banners_categories) as a,(SELECT id from jos_banners) as b;
+----+----+
| id | id |
+----+----+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
+----+----+
3 rows in set (0.00 sec)

Ramurd · 05-12-2011, 09:12 AM

I presume you have 1 table that in your example would have the following fields:

Code:

ssn (index ?)
time_up
city
car
...

Given that here's the query that will give the amount of people where the car <> '' and the total; the other one I leave for you as an exercise ;-)

Code:

select (select count(*) from "lq-psql" where not car = '') as withcar, count(*) from "lq-psql" as total;