[SQL] Select last there dates

[Loredana Curugiu]

Hello again,

I have the following two tables:

Table 1:
uid | phone_number |
-+---
  8 | +40741775621 |
  8 | +40741775622 |
  8 | +40741775623 |
  9 | +40741775621 |
  9 | +40741775622 |
  9 | +40741775623 |
10 | +40741775621 |
10 | +40741775622 |
10 | +40741775623 |
  7 | +40741775621 |
  7 | +40741775622 |
  7 | +40741775623 |
11 | +40741775621 |
11 | +40741775622 |
11 | +40741775623 |

Table2:

  uid | phone_number | date
---+---+---
 8 | +40741775621 | 2007-06-21 10:40:00+00
 8 | +40741775621 | 2007-05-21 10:40:00+00
 8 | +40741775621 | 2007-04-21 10:40:00+00
 8 | +40741775621 | 2007-03-21 10:40:00+00
 8 | +40741775621 | 2007-06-20 10:40:00+00
 8 | +40741775621 | 2007-06-19 10:40:00+00
 8 | +40741775621 | 2007-06-18 10:40:00+00
 8 | +40741775622 | 2007-06-16 10:40:00+00
 8 | +40741775622 | 2007-06-15 10:40:00+00
 7 | +40741775622 | 2007-06-21 05:54:13.646457+00
 7 | +40741775621 | 2007-06-21 05:54:21.134469+00


For each uid  column from table1 I have different values phone_number
column.

For each uid and phone_number columns from table2 I have different
values for date column.

My task is to create a query which for a given uid returns all values
for phone_number column from table1 and last three values of date
column from table2.

For example, if uid=8 the query should return:

phone_number |date
---+
+40741775621 | 2007-06-21, 2007-06-20, 2007-06-19
+40741775622 | 2007-06-16, 2007-06-15
+40741775623 |

I created the query

   SELECT table1.phone_number,
 TO_CHAR( table2.date, '-MM-DD' ) AS date
  FROM ( SELECT * FROM table1 WHERE uid=8 ) table1
LEFT JOIN table2
   ON table1.uid=8
 AND table1.uid=table2.uid
 AND table1.phone_number=table2.phone_number

which returns.

phone_number |date
--+
+40741775621 | 2007-06-18
+40741775621 | 2007-06-19
+40741775621 | 2007-06-20
+40741775621 | 2007-03-21
+40741775621 | 2007-04-21
+40741775621 | 2007-05-21
+40741775621 | 2007-06-21
+40741775622 | 2007-06-15
+40741775622 | 2007-06-16
+40741775623 |

I don't know how to use this result for obtaining the result I need.
I was thinking to get the dates for the phone_number into a array
and then to use array_to string  function, but I don't know how to do it.
Any help, please?

Loredana

Re: [SQL] Select last there dates

[Richard Huxton]

Loredana Curugiu wrote:

My task is to create a query which for a given uid returns all values
for phone_number column from table1 and last three values of date
column from table2.

For example, if uid=8 the query should return:

phone_number |date
---+
+40741775621 | 2007-06-21, 2007-06-20, 2007-06-19
+40741775622 | 2007-06-16, 2007-06-15
+40741775623 |


You either need a subquery with a LIMIT, or you could write a custom 
aggregate (see below):


BEGIN;

CREATE TABLE telnum_date_test (
telnum  text,
teldate date
);
INSERT INTO telnum_date_test SELECT '0123 456 789','2007-01-10'::date - 
generate_series(0,9);
INSERT INTO telnum_date_test SELECT '0234 567 890','2007-02-10'::date - 
generate_series(0,9);


SELECT * FROM telnum_date_test ORDER BY telnum,teldate;


CREATE FUNCTION date_top3_acc(topvals date[], newval date) RETURNS 
date[] AS $$

DECLARE
i   int4;
j   int4;
n   int4;
outvals date[];
BEGIN
-- array_upper returns null for an empty array and 1 for a 1 
element array

n := COALESCE( array_upper(topvals, 1), 0 );
j := 1;

-- I suppose you could think of this as an insert-sort with an 
upper bound

FOR i IN 1..n LOOP
IF newval > topvals[i] AND j <= 3 THEN
outvals[j] := newval;
j := j + 1;
END IF;
IF j <= 3 THEN
outvals[j] := topvals[i];
j := j + 1;
END IF;
END LOOP;

IF j <= 3 THEN
outvals[j] := newval;
END IF;

RETURN outvals;
END;
$$ LANGUAGE plpgsql;


CREATE AGGREGATE top3(date) (
sfunc = date_top3_acc,
stype = date[],
initcond = '{}'
);

SELECT telnum, top3(teldate) FROM telnum_date_test GROUP BY telnum ORDER 
BY telnum;


COMMIT;


--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

[SQL] Results per letter query

[Dani Castaños]

Hi!

I'm trying to build a query to get if there is an occurrence for a field 
for each alphabetical letter.

My first thought to know it was to do something like:

SELECT COUNT(field) FROM table WHERE UPPER( field )  LIKE UPPER( 'A%' ) 
LIMIT 1;
SELECT COUNT(field) FROM table WHERE UPPER( field )  LIKE UPPER( 'B%' ) 
LIMIT 1;
SELECT COUNT(field) FROM table WHERE UPPER( field )  LIKE UPPER( 'C%' ) 
LIMIT 1;

...
and so on...

Is there any way to do it in only one query??

Thank you in advance!

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] Select last there dates

[A. Kretschmer]

am  Thu, dem 21.06.2007, um 11:18:13 +0300 mailte Loredana Curugiu folgendes:
> Hello again,
> 
> I have the following two tables:
> 
> Table 1:
> uid | phone_number |
> -+---
>8 | +40741775621 |
>8 | +40741775622 |
>8 | +40741775623 |
>9 | +40741775621 |
>9 | +40741775622 |
>9 | +40741775623 |
>  10 | +40741775621 |
>  10 | +40741775622 |
>  10 | +40741775623 |
>7 | +40741775621 |
>7 | +40741775622 |
>7 | +40741775623 |
>  11 | +40741775621 |
>  11 | +40741775622 |
>  11 | +40741775623 |
> 
> Table2:
> 
>uid | phone_number | date
>  ---+---+---
>   8 | +40741775621 | 2007-06-21 10:40:00+00
>   8 | +40741775621 | 2007-05-21 10:40:00+00
>   8 | +40741775621 | 2007-04-21 10:40:00+00
>   8 | +40741775621 | 2007-03-21 10:40:00+00
>   8 | +40741775621 | 2007-06-20 10:40:00+00
>   8 | +40741775621 | 2007-06-19 10:40:00+00
>   8 | +40741775621 | 2007-06-18 10:40:00+00
>   8 | +40741775622 | 2007-06-16 10:40:00+00
>   8 | +40741775622 | 2007-06-15 10:40:00+00
>   7 | +40741775622 | 2007-06-21 05:54:13.646457+00
>   7 | +40741775621 | 2007-06-21 05:54:21.134469+00
> 
> 
> For each uid  column from table1 I have different values phone_number
> column.
> 
> For each uid and phone_number columns from table2 I have different
> values for date column.
> 
> My task is to create a query which for a given uid returns all values
> for phone_number column from table1 and last three values of date 
> column from table2.
> 
> For example, if uid=8 the query should return:
> 
>  phone_number |date
> ---+
>  +40741775621 | 2007-06-21, 2007-06-20, 2007-06-19
>  +40741775622 | 2007-06-16, 2007-06-15
>  +40741775623 |

lets try:

first, i need a comma-aggregat:

CREATE FUNCTION comma_aggregate(text,text) RETURNS text AS ' 
SELECT CASE WHEN $1 <>  THEN $1 || '', '' || $2 ELSE $2 END; ' 
LANGUAGE sql IMMUTABLE STRICT; 

CREATE AGGREGATE comma (basetype=text, sfunc=comma_aggregate, stype=text, 
initcond='' );


Now your tables, with a typo in the phone_number - column, sorry ;-)

test=*# select * from t1;
 uid | phone_numer
-+-
   8 | 40741775621
   8 | 40741775622
   8 | 40741775623
   9 | 40741775621
   9 | 40741775622
   9 | 40741775623
  10 | 40741775621
  10 | 40741775622
  10 | 40741775623
(9 rows)

test=*# select * from t2;
 uid | phone_numer |   datum
-+-+
   8 | 40741775621 | 2007-06-21
   8 | 40741775621 | 2007-05-21
   8 | 40741775621 | 2007-04-21
   8 | 40741775621 | 2007-03-21
   8 | 40741775621 | 2007-06-20
   8 | 40741775621 | 2007-06-19
   8 | 40741775621 | 2007-06-18
   8 | 40741775622 | 2007-06-16
   8 | 40741775622 | 2007-06-15
   7 | 40741775622 | 2007-06-21
   7 | 40741775621 | 2007-06-21
(11 rows)


And now:

test=*# select t1.phone_numer, substring(comma(t2.datum) from 1 for 34)
from t1 left outer join (select uid, phone_numer, datum from t2 order by
2 ) t2 on (t1.uid,t1.phone_numer)=(t2.uid,t2.phone_numer) where t1.uid=8
group by t1.phone_numer;
 phone_numer | substring
-+
 40741775621 | 2007-06-21, 2007-05-21, 2007-04-21
 40741775622 | 2007-06-16, 2007-06-15
 40741775623 |
(3 rows)


Perhaps there are better solutions possible...



Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] Results per letter query

[A. Kretschmer]

am  Thu, dem 21.06.2007, um 11:10:02 +0200 mailte Dani Castaños folgendes:
> Hi!
> 
> I'm trying to build a query to get if there is an occurrence for a field 
> for each alphabetical letter.
> My first thought to know it was to do something like:
> 
> SELECT COUNT(field) FROM table WHERE UPPER( field )  LIKE UPPER( 'A%' ) 
> LIMIT 1;
> SELECT COUNT(field) FROM table WHERE UPPER( field )  LIKE UPPER( 'B%' ) 
> LIMIT 1;
> SELECT COUNT(field) FROM table WHERE UPPER( field )  LIKE UPPER( 'C%' ) 
> LIMIT 1;
> ...
> and so on...
> 
> Is there any way to do it in only one query??

I'm not sure if i understand you correctly, sorry, if not.

test=*# select * from w;
   t

 test
 foo
 bar
 foobar
(4 rows)

test=*# select chr(x), count(1) from generate_series(65,90) x, w where
upper(substring (w.t from 1 for 1)) ~ chr(x) group by 1;
 chr | count
-+---
 T   | 1
 B   | 1
 F   | 2
(3 rows)



Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [SQL] Results per letter query

[Dani Castaños]

Hi!

I'm trying to build a query to get if there is an occurrence for a field 
for each alphabetical letter.

My first thought to know it was to do something like:

SELECT COUNT(field) FROM table WHERE UPPER( field )  LIKE UPPER( 'A%' ) 
LIMIT 1;
SELECT COUNT(field) FROM table WHERE UPPER( field )  LIKE UPPER( 'B%' ) 
LIMIT 1;
SELECT COUNT(field) FROM table WHERE UPPER( field )  LIKE UPPER( 'C%' ) 
LIMIT 1;

...
and so on...

Is there any way to do it in only one query??



I'm not sure if i understand you correctly, sorry, if not.

test=*# select * from w;
   t

 test
 foo
 bar
 foobar
(4 rows)

test=*# select chr(x), count(1) from generate_series(65,90) x, w where
upper(substring (w.t from 1 for 1)) ~ chr(x) group by 1;
 chr | count
-+---
 T   | 1
 B   | 1
 F   | 2
(3 rows)



Andreas
  
It's exactly what i want. Just one more thing... What if i want also the 
ones that begin by a non-alphabetical character.

In your example:

test=*# select * from w;
  t

test
foo
bar
foobar
1foobar
/ertw
@weras


and have:
chr | count
-+---
T   | 1
B   | 1
F   | 2
_   | 3
(4 rows)


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [SQL] Results per letter query

[A. Kretschmer]

am  Thu, dem 21.06.2007, um 12:42:52 +0200 mailte Dani Castaños folgendes:
> >test=*# select chr(x), count(1) from generate_series(65,90) x, w where
> >upper(substring (w.t from 1 for 1)) ~ chr(x) group by 1;
> > chr | count
> >-+---
> > T   | 1
> > B   | 1
> > F   | 2
> >(3 rows)
> >
> >
> >
> >Andreas
> >  
> It's exactly what i want. Just one more thing... What if i want also the 
> ones that begin by a non-alphabetical character.
> In your example:

change the generate_series(65,90) to generate_series(32,90)


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] Results per letter query

[Dani Castaños]

am  Thu, dem 21.06.2007, um 12:42:52 +0200 mailte Dani Castaños folgendes:
  

test=*# select chr(x), count(1) from generate_series(65,90) x, w where
upper(substring (w.t from 1 for 1)) ~ chr(x) group by 1;
chr | count
-+---
T   | 1
B   | 1
F   | 2
(3 rows)



Andreas
 
  
It's exactly what i want. Just one more thing... What if i want also the 
ones that begin by a non-alphabetical character.

In your example:



change the generate_series(65,90) to generate_series(32,90)


Andreas
  

With only changing 65 to 32:

ERROR:  invalid regular expression: parentheses () not balanced

I think, it could be a problem with UPPER and non alphabetical chars
--

*Dani Castaños Sánchez*
[EMAIL PROTECTED] 

ANDROME Iberica
http://www.androme.es
Constança, 5, 08029 Barcelona
Tel: +34 934948850
Fax: +34 934196094


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] Results per letter query

[A. Kretschmer]

am  Thu, dem 21.06.2007, um 12:59:05 +0200 mailte Dani Castaños folgendes:
> >change the generate_series(65,90) to generate_series(32,90)
> >
> >
> >Andreas
> >  
> With only changing 65 to 32:
> 
> ERROR:  invalid regular expression: parentheses () not balanced
> 
> I think, it could be a problem with UPPER and non alphabetical chars

No, the ~ - operator (Regex), try this:

select chr(x), count(1) from generate_series(32,90) x, w where upper(substring 
(w.t from 1 for 1)) = chr(x) group by 1;


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] Select last there dates

[Loredana Curugiu]

Richard, Andreas,

thank you very much for your solutions. I took a look on
both solutions, but I choosed Andreas's solution because
is shorter :)

So Andreas, would you please give some more explanations
on your solution? I didn't work with functions and aggregate till
now.

I don't understand how this comma_aggregate works. I can see that this
function it is defined with two arguments, but the aggredate it is called
with a single argument.

And what is LANGUAGE sql IMMUTABLE STRICT ?

CREATE FUNCTION comma_aggregate(text,text) RETURNS text AS '

SELECT CASE WHEN $1 <>  THEN $1 || '', '' || $2 ELSE $2 END; '
LANGUAGE sql IMMUTABLE STRICT;

CREATE AGGREGATE comma (basetype=text, sfunc=comma_aggregate, stype=text,
initcond='' );




Best regards,
 Loredana

Re: [SQL] Select last there dates

[Richard Huxton]

Loredana Curugiu wrote:

Richard, Andreas,

thank you very much for your solutions. I took a look on
both solutions, but I choosed Andreas's solution because
is shorter :)


Not to mention clever, exploiting the fact that we know the length of a 
text-representation of three comma-separated dates.


I think there might be a small typo though. The left-join is to:
  (select uid, phone_numer, datum from t2 order by 2 )
Probably want to order by "datum DESC" too, to ensure you get the latest 
dates for each telnum.



So Andreas, would you please give some more explanations
on your solution? I didn't work with functions and aggregate till
now.

I don't understand how this comma_aggregate works. I can see that this
function it is defined with two arguments, but the aggredate it is called
with a single argument.


The comma_aggregate function takes two parameters (RUNNING-TOTAL, 
NEW-VALUE). The result forms the RUNNING-TOTAL for the next call. This 
RUNNING-TOTAL has a type set by "stype" below and with an initial value 
set by "initcond".



And what is LANGUAGE sql IMMUTABLE STRICT ?


The function is pure SQL (mine was procedural plpgsql, other languages 
are available). It's IMMUTABLE because the output depends only on the 
inputs, not the contents of the database and begin STRICT if input 
values are NULL then the output is automatically NULL.



CREATE FUNCTION comma_aggregate(text,text) RETURNS text AS '

SELECT CASE WHEN $1 <>  THEN $1 || '', '' || $2 ELSE $2 END; '
LANGUAGE sql IMMUTABLE STRICT;

CREATE AGGREGATE comma (basetype=text, sfunc=comma_aggregate, stype=text,
initcond='' );



--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [SQL] Results per letter query

[Dani Castaños]

A. Kretschmer escribió:

am  Thu, dem 21.06.2007, um 12:59:05 +0200 mailte Dani Castaños folgendes:
  

change the generate_series(65,90) to generate_series(32,90)


Andreas
 
  

With only changing 65 to 32:

ERROR:  invalid regular expression: parentheses () not balanced

I think, it could be a problem with UPPER and non alphabetical chars



No, the ~ - operator (Regex), try this:

select chr(x), count(1) from generate_series(32,90) x, w where upper(substring 
(w.t from 1 for 1)) = chr(x) group by 1;


Andreas
  

Thanks Andreas!
I thik i've found a better solution for my problem:

I got another solution, but I've used EXPLAIN ANALYZE, and yours is better:

EXPLAIN ANALYZE select chr(x), count(1) from generate_series(32,90) x, 
sip_customer_services where upper(substring 
(sip_customer_services.service_name from 1 for 1)) = chr(x) group by 1;

QUERY PLAN

HashAggregate  (cost=37.00..39.25 rows=150 width=4) (actual 
time=1.652..1.716 rows=13 loops=1)
  ->  Hash Join  (cost=9.38..36.25 rows=150 width=4) (actual 
time=0.979..1.490 rows=18 loops=1)
Hash Cond: (chr("outer".x) = 
upper("substring"(("inner".service_name)::text, 1, 1)))
->  Function Scan on generate_series x  (cost=0.00..12.50 
rows=1000 width=4) (actual time=0.114..0.332 rows=59 loops=1)
->  Hash  (cost=9.30..9.30 rows=30 width=10) (actual 
time=0.647..0.647 rows=18 loops=1)
  ->  Seq Scan on sip_customer_services  (cost=0.00..9.30 
rows=30 width=10) (actual time=0.295..0.442 rows=18 loops=1)

Total runtime: 2.147 ms
(7 rows)

test=# EXPLAIN ANALYZE select count(*), upper(substr(service_name, 1, 
1)) from sip_customer_services group by upper(substr(service_name, 1,1));

   QUERY PLAN

HashAggregate  (cost=9.60..10.13 rows=30 width=10) (actual 
time=0.704..0.766 rows=13 loops=1)
  ->  Seq Scan on sip_customer_services  (cost=0.00..9.45 rows=30 
width=10) (actual time=0.332..0.530 rows=18 loops=1)

Total runtime: 1.065 ms
(3 rows);

Thank you very much, anyway!

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [SQL] Select last there dates

[A. Kretschmer]

am  Thu, dem 21.06.2007, um 16:00:05 +0300 mailte Loredana Curugiu folgendes:
> So Andreas, would you please give some more explanations
> on your solution? I didn't work with functions and aggregate till
> now.

I will try it, but i'm not a nativ english speaker and thats why i have
some problems...

> 
> I don't understand how this comma_aggregate works. I can see that this
> function it is defined with two arguments, but the aggredate it is called
> with a single argument.

An aggregate needs among others a so called 'SFUNC' with 2 parameters:
internal-state and next data item. It returns the next-internal-state.

This is explained here:
http://www.postgresql.org/docs/8.1/interactive/sql-createaggregate.html

> 
> And what is LANGUAGE sql IMMUTABLE STRICT ?

An IMMUTABLE function can't modify the database
http://www.postgresql.org/docs/current/static/xfunc-volatility.html


STRICT:
http://www.postgresql.org/docs/8.2/static/xfunc-c.html

,[  ]
| Notice that we have specified the functions as "strict", meaning that
| the system should automatically assume a null result if any input value
| is null.
`



Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [SQL] Select last there dates

[Loredana Curugiu]

I think there might be a small typo though. The left-join is to:
   (select uid, phone_numer, datum from t2 order by 2 )
Probably want to order by "datum DESC" too, to ensure you get the latest
dates for each telnum.



yes, I also observed this, so I have add to my query.

Thank you for your explanations, Richard.


Best regards,
  Loredana

Re: [NOVICE] [SQL] Select last there dates

[Loredana Curugiu]

Thank you for documentation.

Best regards,
   Loredana

[SQL] join problem

[A. R. Van Hook]

I have three tables relating to purchases
 invoice  - transaction data (customer id, deposit. etc)
 invoiceitems - purachace items detail
 cai  - customer data
if I query for the total charges using
  select sum(rowtot + tax)
   from invoiceitems
  where ivid in (select ivid from invoice where cusid = 2128)"

I get 1179.24 which is correct.

if I query for the total deposit using
  select sum(deposit)
  from invoice
  where cusid = 2128"

I also get 1179.24, also the correct amount


If I try an inclusive query using the following:
select
 sum(i.rowtot + i.tax) as tot,
 sum(v.deposit) as deposit
   from cai c
   join invoice   v on (v.cusid = c.cusid)
   left join invoiceitems i on (v.ivid = i.ivid)
   where v.cusid = 2128
   group by 
 c.cusid

I get
   tot| deposit
--+-
1179.240 | 2819.24

Can someone correct the query?


thanks



tables definations are as follows:

invoice 
   (ividint NOT NULL PRIMARY KEY,

rid int null references registry,
sid int not null  references staffname,
cusid   int,
invdate date,
ifname  varchar(16),
imi char,
ilname  varchar(16),
addrtext,
cityvarchar(16),
state   varchar(2),
zip varchar(16),
iphone  varchar(16),
eventdate   date,
paytype int,
bustvarchar(16),
height  varchar(16),
dressizevarchar(16),
waist   varchar(16),
hipsvarchar(16),
hollow  varchar(16),
deposit numeric(6,2),
transtype   int,
notes   text,
neckvarchar(16),
arm_length  varchar(16),
leg_length  varchar(16),
coatvarchar(16),
shoevarchar(16),
tux intdefault 0

invoiceItems 
   (itemint NOT NULL,
ividint NOT NULL references invoice ON DELETE 
CASCADE,

qty int,
stidint  references stock, /*tag*/
descripttext,
price   numeric(6,2),
tax numeric(7,3),
discountnumeric(6,2),
rowtot  numeric(7,3),
pickup  int default 0,   /* SO or to be picked up = 
1 */

primary key(item, ivid)

create table cai/* customer account  information*/
  (cusidint NOT null primary key,
   cfname   varchar(16),
   cmi  char  default '',
   clname   varchar(16),
   caddrtext,
   ccityvarchar(16),
   cstate   varchar(2),
   czip varchar(16),
   cphone   varchar(16),
   db   numeric(7,2),
   tcodeint not null default 0,
   acodeint not null default 0,
   tdatetimestamp not null
[EMAIL PROTECTED] ~]$


--
Arthur R. Van Hook
Mayor - Retired
The City of Lake Lotawana

[EMAIL PROTECTED]
[EMAIL PROTECTED]
[EMAIL PROTECTED]
(816) 578-4704 - Home




---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [SQL] Subquery problems

[Masaru Sugawara]

On Tue, 19 Jun 2007 09:17:22 -0300
Ranieri Mazili <[EMAIL PROTECTED]> wrote:

Hi,

This reply is not accurate, but I think there are helpful hints.

--
Masaru Sugawara




select 
  C.id_production_area,
  B.id_machine_type,
  A.h_month as month,
  max(A.n) as div_mes,
  cast((sum(A.qty_employees_total)
   -(sum(A.qty_absence)
   -sum(A.qty_vacation)
   -sum(A.qty_diseased)
   -sum(A.qty_indirect)
   -sum(A.qty_transferred))
   +sum(A.qty_received))/max(A.n) as integer)
from (select * 
   from  
   (select *, extract(month from h1.head_count_date) as h_month
   from head_count as h1
   where extract(year from h1.head_count_date) 
   = extract(year from current_date)
) as h2,
(select extract(month from production_date) as p_month,
   count(distinct p1.production_date) as n
   from production as p1
   where extract(year from production_date) 
   = extract(year from current_date)
   group by extract(month from production_date)
 ) as p2
 where h2.h_month = p2.p_month
  ) as A, machine B, machine_type C
where  A.id_machine = B.id_machine
   AND B.id_machine_type = C.id_machine_type
group by C.id_production_area, B.id_machine_type, A.h_month
order by C.id_production_area, A.h_month, A.h_month DESC;





> Hello,
> 
> I'm having another "problem", I have a function that declares 12 
> variable, one per month and each them execute a select like bellow:
> *DIV_MES01 := (select count(distinct production_date) from production 
> where extract(month from production_date) = '01' and extract(year from 
> production_date) = EXTRACT(YEAR FROM current_date));
> 
> *Then, I need to check if the variable is equal 0:
> *IF DIV_MES01 = 0 THEN
>DIV_MES01 := 1;
> END IF;
> 
> *Finally,  I perform the following query:
> 
> *SELECTcast(((sum(A.qty_employees_total)
>   -(sum(A.qty_absence)
>   -sum(A.qty_vacation)
>   -sum(A.qty_diseased)
>   -sum(A.qty_indirect)
>   -sum(A.qty_transferred))
>   +sum(A.qty_received))/DIV_MES01) AS integer),
>   C.id_production_area,
>   cast('01' as text) AS mes
> FROM  head_count A, machine B, machine_type C
> WHERE EXTRACT(YEAR FROM head_count_date) = EXTRACT(YEAR FROM 
> current_date)
>   AND EXTRACT(MONTH FROM head_count_date) = '01'
>   AND A.id_machine = B.id_machine
>   AND B.id_machine_type = C.id_machine_type
> GROUP BY C.id_production_area, B.id_machine_type
> 
> *Doing it, I need to perform 12 querys united by "UNION", what I want to 
> do is unify it in only one query, I tryed with the query bellow:
> 
> *SELECT date_trunc('month', A.head_count_date)::date as head_date,
>cast(((sum(A.qty_employees_total)
>-(sum(A.qty_absence)
>-sum(A.qty_vacation)
>-sum(A.qty_diseased)
>-sum(A.qty_indirect)
>-sum(A.qty_transferred))
>+sum(A.qty_received))/(select   count(distinct production_date)
>   from production
>   whereextract(month from 
> production_date) = EXTRACT(MONTH FROM date_trunc('month', 
> A.head_count_date)::date)
>and extract(year from 
> production_date) = EXTRACT(YEAR FROM current_date))) AS integer),
>C.id_production_area
> FROM head_count A, machine B, machine_type C
> WHEREdate_trunc('month', A.head_count_date)::date BETWEEN
>  date_trunc('month', current_date - (EXTRACT(MONTH FROM 
> current_date)-1) * interval '1 month')::date
>  AND date_trunc('month', current_date)::date
>  AND A.id_machine = B.id_machine
>  AND B.id_machine_type = C.id_machine_type
> GROUP BY C.id_production_area, B.id_machine_type, head_count_date,head_date
> ORDER BY id_production_area, head_count_date,head_date DESC
> 
> *But the results aren't what I want.
> What I trying to do is possible?
> 
> I appreciate any help.
> Thanks
> 




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

Re: [SQL] join problem

[Ragnar]

On fim, 2007-06-21 at 08:46 -0500, A. R. Van Hook wrote:
> if I query for the total deposit using
>select sum(deposit)
>from invoice
>where cusid = 2128"
> 
> I also get 1179.24, also the correct amount
> 
> 
> If I try an inclusive query using the following:
>  select
>   sum(i.rowtot + i.tax) as tot,
>   sum(v.deposit) as deposit
> from cai c
> join invoice   v on (v.cusid = c.cusid)
> left join invoiceitems i on (v.ivid = i.ivid)
> where v.cusid = 2128
> group by 
>   c.cusid
> I get
> tot| deposit
> --+-
>  1179.240 | 2819.24

you are adding the invoice deposit once for each item

gnari



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [SQL] Constraint exclusion

[Fernando Hevia]

I see. Thanks for the tip.

Regards,
Fernando.

-Mensaje original-
De: Tom Lane [mailto:[EMAIL PROTECTED] 
Enviado el: Miércoles, 20 de Junio de 2007 19:37
Para: Fernando Hevia
CC: 'PostgreSQL SQL List'
Asunto: Re: [SQL] Constraint exclusion 

"Fernando Hevia" <[EMAIL PROTECTED]> writes:
> -- Constraints: one partition per month
> ALTER TABLE table_p01 ADD CONSTRAINT chk_table_p01_setuptime CHECK
> (EXTRACT(MONTH FROM setuptime) =  1::DOUBLE PRECISION);

The planner is not able to do anything with these constraints, other
than if there is an exact match to them in the query WHERE, which
there is not.  Try simple range constraints on the column, instead.
The system does know about inferences like "colx <= const1 must
imply colx <= const2 if const1 <= const2".  It does not know how
to reason about extract().

regards, tom lane


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq