On Sat, 15 Sep 2001 00:26:01 +0200, I wrote:
> [...]
>CREATE VIEW mj1 (jid, cnt) AS
>SELECT jid, COUNT(mid) cnt FROM mj GROUP BY jid;
This should be COUNT(mid) AS cnt ...
> [...]
>I did not test this with PostgreSQL, but you get the idea.
Well, now I did test with PostgreSQL (thanks, Jason Tis
Joseph,
you might want to try:
CREATE VIEW mj1 (jid, cnt) AS
SELECT jid, COUNT(mid) cnt FROM mj GROUP BY jid;
CREATE VIEW ml1 (jid, cnt) AS
SELECT jid, COUNT(*) cnt FROM ml WHERE state <> 11 GROUP BY jid;
CREATE VIEW ml2 (jid, cnt) AS
SELECT jid, COUNT(*) cnt FROM ml WHERE state IN (2,5) GROUP
Would this give you what you want?
SELECT j.id, j.created, COUNT(mj.mid),
SUM(CASE WHEN ml.state <> 11 THEN 1 ELSE 0 END) AS tally_1,
SUM (CASE WHEN ml.state IN(2,5) THEN 1 ELSE 0 END)AS tally_2
FROM j, mj, ml
WHERE j.fkey = 1
AND mj.jid = j.id
AND ml.jid = j.id;
Josh Berkus wrote:
> Try putting your sub-selects in the FROM clause instead. (Personally,
> I've never found a use for sub-selects in the SELECT clause)
>
> SELECT j.id, j.created, count(mj.mid), ma1.mcount, ma2.mcount
> FROM j, mj,
> (SELECTjid, COUNT(oid) as mcount FROM ml
>
Josh Berkus wrote:
> Joseph,
>
> The subject line could describe a lot of what I see outside my house
> every day (I live in San Francisco CA).
>
>
>>Could someome explain these error messages to me? Why am I being
>>asked to group by j.id?
>>
>
> Because you've asked the db engine to coun
Joseph,
The subject line could describe a lot of what I see outside my house
every day (I live in San Francisco CA).
> Could someome explain these error messages to me? Why am I being
> asked to group by j.id?
Because you've asked the db engine to count on mj.mid. The parser want
you to be s
Could someome explain these error messages to me? Why am I being asked to group by
j.id?
And why is the subquery worried about ml.oid if ml.oid is used in an aggregate?
Follows: script, then output.
select version();
create table j (id int, created timestamp default current_timestamp, fkey
