[SQL] combine SQL SELECT statements into one
Good Evening, Good Morning Wherever you are whenever you may be reading this. I am new to this email group and have some good experience with SQL and PostgreSQL database. I am currently working on a PHP / PostgreSQL project and I came upon something I could not figure out in SQL. I was wondering if anyone here could take a look and perhaps offer some guidance or assistance in helping me write this SQL query. Please Consider the following information: --- I have a postgresql table called 'inventory' that includes two fields: 'model' which is a character varying field and 'modified' which is a timestamp field. So the table inventory looks something like this: model modified - -- I778288176 2010-02-01 08:27:00 I778288176 2010-01-31 11:23:00 I778288176 2010-01-29 10:46:00 JKLM112345 2010-02-01 08:25:00 JKLM112345 2010-01-31 09:52:00 JKLM112345 2010-01-28 09:44:00 X22TUNM765 2010-01-17 10:13:00 V8893456T6 2010-01-01 09:17:00 Now with the table, fields and data in mind look at the following three queries: SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-02-01'; SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-20'; SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-01'; All three of the above queries work and provide results. However, I want to combine the three into one SQL Statement that hits the database one time. How can I do this in one SQL Statement? Is it possible with sub select? Here is what result I am looking for from one SELECT statement using the data example from above: count1 | count2 | count3 --- 2 2 4 Can this be done with ONE SQL STATEMENT? touching the database only ONE time? Please let me know. Thanx> :) NEiL
[SQL] string functions and operators
Hello,
I have a dilema and I was hoping someone here may offer guidance or assistance.
I bet this is a very simple question for someone out there but I am having
problems coming up with a solution. Here it is...
suppose I have a field with the following values:
77.1
77.2
134.1
134.2
134.3
5.1
5.2
I need two seperate SELECT queries. One would return the following values
(everything left of the decimal point)
77
77
134
134
5
5
The second query would return all of the values to the right of the decimal
point:
1
2
1
2
3
1
2
Now, I have been using the following information (although very Greek) to try
to solve this problem:
http://www.postgresql.org/docs/current/static/functions-string.html
And I have been playing around with the syntax of the following:
substring('112.5' from '%#"___.#"_' for '#')
but the aforementioned is not quite working out... can someone please show me a
string function that will produce the desired results?
Thanks!
~n
Re: [SQL] string functions and operators
This is good, however, I need only the numbers to the right of the decimal
point
so if my number if 17.2
I would need one query that would return 17 (your function will do that)
and the second query would return: 2
not 0.2
just 2
Does that make sense?
From: Petru Ghita
To: Neil Stlyz ; pgsql-sql mailing list
Sent: Mon, March 22, 2010 8:08:30 PM
Subject: Re: [SQL] string functions and operators
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
For numeric data types use:
http://www.postgresql.org/docs/8.4/static/functions-math.html
You could then use|floor|(dp or numeric)|| for example:
postgres=# select floor(71.912);
floor
- ---
71
(1 row)
postgres=# select 71.912-floor(71.912);
?column?
- --
0.912
But as you might have negative numbers in there I guess you should
abs() the values like in:
postgres=# select abs(71.912)-floor(abs(71.912));
?column?
- --
0.912
postgres=# select abs(-171.912)-floor(abs(-171.912));
?column?
- --
0.912
(1 row)
On 23/03/2010 2:50, Petru Ghita wrote:
> That field of yours... what type is it? Is it TEXT? is it a numeric
> type? If it's TEXT, why don't you make it say... NUMERIC(/10/,
> /6///)?
>
> http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL
>
>
>
>
On 23/03/2010 2:20, Neil Stlyz wrote:
>> Hello,
>
>
>
>> I have a dilema and I was hoping someone here may offer guidance
>
>> or assistance. I bet this is a very simple question for someone
>
>> out there but I am having problems coming up with a solution.
> Here
>
>> it is...
>
>
>
>> suppose I have a field with the following values:
>
>
>
>> 77.1 77.2 134.1 134.2 134.3 5.1 5.2
>
>
>
>> I need two seperate SELECT queries. One would return the
> following
>
>> values (everything left of the decimal point)
>
>
>
>> 77 77 134 134 5 5
>
>
>
>> The second query would return all of the values to the right of
>
>> the decimal point:
>
>
>
>> 1 2 1 2 3 1 2
>
>
>
>
>
>> Now, I have been using the following information (although very
>
>> Greek) to try to solve this problem:
>
>
>
>
> http://www.postgresql.org/docs/current/static/functions-string.html
>
>
>
>
> And I have been playing around with the syntax of the following:
>
>
>
>> substring('112.5' from '%#"___.#"_' for '#')
>
>
>
>> but the aforementioned is not quite working out... can someone
>
>> please show me a string function that will produce the desired
>
>> results?
>
>
>
>> Thanks! ~n
>
>
>
>
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkuoIp4ACgkQt6IL6XzynQQ9igCfRjfOhKXjYZ4gaP3b/4qYqswb
qXMAoJcXbdB3BvCSJ7QH2PwAPMZpAdib
=OY7b
-END PGP SIGNATURE-
