Query query

Query query

Post by Andrew Be » Thu, 29 Nov 2001 04:21:27



Hi,

You have all been so helpful, I'm going to bother you with another.  Say
that you have a table with two categories.  I want to do a *single* query
that shows the number of things in each of the primary category that match
a constraint and
the number of things that don't match that constraint.

Given the following table and the constraint cat2 = 1

cat1  |  cat2

A        1
B        1
A        2
B        2
B        3
B        3

I want to generate output that looks like:

cat1  | count  | count
A         1        1
B         1        3

Where the first 'count' represents the number of things that match the
constraint, and the second 'count' represents the number of things that
doesn't match the constraint.

Can this be done?  If so how?

Thanks,

-- Andrew Bell

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

 
 
 

Query query

Post by Andrew McMill » Thu, 29 Nov 2001 20:27:02



> Given the following table and the constraint cat2 = 1

> cat1  |  cat2

> A        1
> B        1
> A        2
> B        2
> B        3
> B        3

> I want to generate output that looks like:

> cat1  | count  | count
> A         1        1
> B         1        3

> Where the first 'count' represents the number of things that match the
> constraint, and the second 'count' represents the number of things that
> doesn't match the constraint.

> Can this be done?  If so how?

test=# select distinct cat1, (select count(*) from t t_1 where t_1.cat1
= t.cat1 AND t_1.cat2 = 1) as count, (select count(*) from t t_2 where
t_2.cat1 = t.cat1 and t_2.cat2 != 1) as count from t;
 cat1 | count | count
------+-------+-------
 A    |     1 |     1
 B    |     1 |     3
(2 rows)

test=# select version();
                            version                            
---------------------------------------------------------------
 PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.95.4
(1 row)

But I would keep a weather eye on the costs for this sort of query -
just because it can be done in SQL like this doesn't necessarily mean it
_should_ be done!.

Triggers maintaining summary data on a joined table may be a more
efficient approach, depending on data volumes and rates of inserts /
queries.

test=# explain select distinct cat1, (select count(*) from t t_1 where
t_1.cat1 = t.cat1 AND t_1.cat2 = 1) as count, (select count(*) from t
t_2 where t_2.cat1 = t.cat1 and t_2.cat2 != 1) as count from t;
NOTICE:  QUERY PLAN:

Unique  (cost=1.14..1.18 rows=1 width=12)
  ->  Sort  (cost=1.14..1.14 rows=6 width=12)
        ->  Seq Scan on t  (cost=0.00..1.06 rows=6 width=12)
              SubPlan
                ->  Aggregate  (cost=1.09..1.09 rows=1 width=0)
                      ->  Seq Scan on t t_1  (cost=0.00..1.09 rows=1
width=0)
                ->  Aggregate  (cost=1.09..1.09 rows=1 width=0)
                      ->  Seq Scan on t t_2  (cost=0.00..1.09 rows=1
width=0)

EXPLAIN

Regards,
                                        Andrew.
--
--------------------------------------------------------------------

WEB: http://catalyst.net.nz/        PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201    MOB: +64(21)635-694    OFFICE: +64(4)499-2267

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command


 
 
 

1. Querying against a DataWindow - or - How to do this query?

I'd like to be able to query a single table of the following format:

(DATE,DEPARTMENT,PRICE,QUANTITY)

And what I'd like to do is something like the following:

Given a range of dates, select the following data:

(DEPARTMENT,THIS YEAR TOTAL PRICE, LAST YEAR TOTAL PRICE)

I can figure out how to do this for _JUST_ this year and for just _LAST_
year.  I would think to potentially do this into two datawindows and
then create a third datawindow that would join based on the DEPARTMENT
field.  But I don't see a way of doing that.  I could do both selects
into individual datawindows, save both datawindows and then import the
data, but that seems like an awfully awkward solution.

If anyone has found a clean way of doing a query like this in PB, I'd
appreciate knowing how.

Thanks in advance,
-Gavin Stark

2. Used Indigo with no keyboard or monitor

3. Nested queries/sub-queries...

4. What is "Socket Notification Sink" ?

5. Subselect query for a multi table insert single query

6. Money 2006 Portfolio two different values for Annualized Return

7. VMware query / Acme query

8. Symantec 6.1 & 6.11 Template bug

9. Vuescan Cropping query

10. zoom factor query

11. TWAIN query