SQL query question

Hi Uwe,

I did a solution for you using PLPgSQL,

create or replace function countem() returns varchar as $$
declare
gcount integer;
xcount integer;
result varchar;
begin
select count(*) into gcount
from pix where image_type = 'G';

select count(*) into xcount
from pix where image_type = 'X';

select gcount || ', ' || xcount
into result;

return result;

end;
$$ LANGUAGE plpgsql;

hope this helps, it's simple and always, there's another (better)
solution
it's my first stab at plpgsql so please bear with me.

-----
Jonel Rienton
http://blogs.road14.com
Software Developer, *nix Advocate

On Feb 3, 2005, at 1:32 AM, Uwe C. Schroeder wrote:

you're right it's late, i better to get to bed myself, i forgot to
throw in the parameter for the user_id in there, i'm sure you can
figure that one out.

regards,

-----
Jonel Rienton
http://blogs.road14.com
Software Developer, *nix Advocate
On Feb 3, 2005, at 1:32 AM, Uwe C. Schroeder wrote:

Am Donnerstag, 3. Februar 2005 08:32 schrieb Uwe C. Schroeder:

Maybe it's to late for me to think correctly (actually I'm sure of
that). I'm going to ask anyways.
I have a table like

id int4
user_id int4
photo varchar
image_type char(1)

where image_type is either G or X
What I want to do is have ONE query that gives me the count of images
of each type per user_id.
So if user 3 has 5 photos of type G and 3 photos of type X
I basically want to have a result 5,3
It got to be possible to get a query like that, but somehow it eludes
me tonight.

Any pointers are greatly appreciated.

UC

select user_id,image_type, count(id)
from <table>
group by user_id,image_type

should do it.

--
Markus Schulz

# uwe@oss4u.com / 2005-02-02 23:32:28 -0800:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Maybe it's to late for me to think correctly (actually I'm sure of
that). I'm going to ask anyways. I have a table like

id int4
user_id int4
photo varchar
image_type char(1)

where image_type is either G or X
What I want to do is have ONE query that gives me the count of images
of each type per user_id.
So if user 3 has 5 photos of type G and 3 photos of type X
I basically want to have a result 5,3

SELECT COUNT(*) FROM t GROUP BY t.user_id, t.image_type

--
If you cc me or remove the list(s) completely I'll most likely ignore
your message. see http://www.eyrie.org./~eagle/faqs/questions.html