Re: [SQL] Your question in postgresql.org forum (Diff. between two times as a numeric value in a stored proc)
Hello Martin, There's not much hint to it, here's a solution instead :) You can make use of several built-in functions to handle strings, date/time values etc. Take a look at chapter 9: Functions and Operators of your PostgreSQL documentation all available functions are explained there. To solve my problem I used the extract EXTRACT (field FROM source) function, as described in paragraph 9.8.1. You can use it to extract a certain field form your timestamp vield, for example: extract (hour from timestamp '2004-16-04 09:21:52') returns 9 SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours'); Result: 442800 EPOCH will return the number of seconds. You can multiply those by n*60, depending if you want minutes or hours. At first I used a different calculation, but this one should be less work :). I hope you're problem is solved that way. I took the liberty of CC-ing the postgresql mailinglist so other's having a similar problem can read about this solution too. It's all about the (open-source)-community isn't it :) Kind regards, Stijn Vanroye -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: dinsdag 15 juni 2004 19:08 To: Stijn Vanroye Subject: Your question in postgresql.org forum Hello Stijn, I read your question in postgresql.org forum published under sunject "Difference between two times as a numeric value in a stored procedure" I am currently solving the same problem... Did you solve it somehow? Can you give a hint please? Best Regards, Martin Tongel ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Difference between two times as a numeric value in a stored procedure. [Scanned]
> "Stijn Vanroye" <[EMAIL PROTECTED]> writes: > > I can't seem to find a way to substract two time values (or > > timestamp values) and get a numeric/float value. I always get the > > INTERVAL datatype. > > extract(epoch from interval) may help. > > regards, tom lane Indeed. I think I should kick myself for missing that, I have read that section af the manual four times or so, and missed it over and over again :) Thanks for the help. Regards, Stijn Vanroye. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Difference between two times as a numeric value in a stored procedure.
Hello List, I am writing two stored procedure which alternatively returns the dayhours and nighthours of two times. (nighthours are considered between 00:00 and 06:00). As an example here is the getdayhours function: CREATE OR REPLACE FUNCTION public.getdayhours(time, time) RETURNS interval AS 'DECLARE begintime ALIAS FOR $1; endtime ALIAS FOR $2; begindate timestamp; enddate timestamp; tmpresult interval; BEGIN IF endtime = time \'00:00\' THEN enddate := (current_date+1)+endtime; ELSE enddate := current_date+endtime; END IF; IF begintime < time \'06:00\' THEN begindate := current_date + time \'06:00\'; ELSE begindate := current_date+begintime; END IF; tmpresult := enddate-begindate; IF tmpresult<\'00:00\' THEN return \'00:00\'; ELSE return tmpresult; END IF; END;' LANGUAGE 'plpgsql' VOLATILE; The working of the functions is not the problem, but the return type is. I can't seem to find a way to substract two time values (or timestamp values) and get a numeric/float value. I always get the INTERVAL datatype. For example, in stead of 4:30 i would like 4.5 as a result. I have searched the documentation but could not find any way to substract time/timestamp values and get a numeric/float as a result. When I try to CAST the interval to a numeric or float value I get an error (cannot cast time without tz to ...). Same goes for trying to cast the beginvalues and then substract them. Does anyone have any idea how I can solve/circumvent this problem? Is there a function I can use? I don't know if it helps but I'm going to use the functions like this: SELECT workhour_id, employee_id, task_id, whdate, begintime, endtime, getdayhours(begintime,endtime), getnighthours(begintime,endtime) FROM workhour Thanks in advance. Stijn Vanroye ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] a wierd query
I understand, thanks. First: as I said in my previous post, the workhour_id was left behind by mistake and has since been removed. So it seems that I can ommit the distinct completely and just use the query in it's simpelest form, like this: select employee_id, begindate as date from workhour UNION select employee_id, enddate as date from workhour And I would get a list of all dates (as well begin- as enddates) where a date can occure only once with each employee? Altough I didn't start this thread I'm learing some usefull things here, so some thanks to the people who replied (and started the thread) are in place here. Kind regards, Stijn Vanroye > -Original Message- > From: Stephan Szabo [mailto:[EMAIL PROTECTED] > Sent: maandag 17 mei 2004 17:01 > To: Stijn Vanroye > Cc: [EMAIL PROTECTED]; Edmund Bacon > Subject: Re: [SQL] a wierd query > > On Mon, 17 May 2004, Stijn Vanroye wrote: > > > Are you sure about that Edmund? > > > > I have the following query: > > select distinct on (task_id, date) task_id, > workhour_id, date from > > ( > > select task_id, workhour_id, begindate as date from workhour > > UNION > > select task_id, workhour_id, enddate as date from workhour > > )as dist1 > > This gets you first rows distincted by task_id, workhour_id and date > and then only rows distincted by task_id and date (and an > unpredictable > workhour_id). > > > if I use the query without the top level select, like this: > > select task_id, workhour_id, begindate as date from workhour > > UNION > > select task_id, workhour_id, enddate as date from workhour > > This gets rows distincted by task_id, workhour_id and date. > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] a wierd query
First of all, I don't select distinct on 1 value, but on 2. Meaning I want each unique combination of task_id (or employee_id in this example) and date. That way both fields still have meaning. the workhour_id field is indeed redundant, but was still there from some pevious testing work. (It is now removed). both other fields are used. What I'm trying to achieve here is the following: for each task get all date's in wich that task has been performed (and each date only once per task). Since workhours have a begin date and time, as well as an end date and time. Begin- and enddate don't have to be the same day. But if a task is performed starting monday and lasting till tueseday, both days have to be included in the count. What I use now is this: select distinct on (date, employee_id) employee_id, date from ( select distinct on (begindate, employee_id) begindate as date, employee_id from workhour UNION select distinct on (enddate, employee_id) enddate as date, employee_id from workhour )as dist The workhour table looks something like this: workhour(workhour_id, task_id, employee_id, begindate, begintime, enddate, endtime) I Can't think of any other solution to achieve this. As far as I can tell, Im not missing something and I don't have meaningless fields (suggestions always welcome). Later on some grouping will be done to count the number of days worked on a certain task (or by a certain employee) in a given period. This still keeps my question open wether or not a UNION does only show unique values in the union. > -Original Message- > From: sad [mailto:[EMAIL PROTECTED] > Sent: maandag 17 mei 2004 9:13 > To: [EMAIL PROTECTED] > Subject: Re: [SQL] a wierd query > > > > I forgot one situation: > > > > if I run the query like so: > > select distinct on (task_id, begindate) task_id, workhour_id, > > begindate as date from workhour UNION > > select distinct on (task_id, enddate) task_id, > workhour_id, enddate > > as date from workhour I get yet another value: 2961 rows. > > > > So I got 3 different result sets for 3 different ways to > run the query. > > Even in this last case the UNION doesn't seem to only > return unique values, > > and I will still need the top-level select. > > if we suppose this situation possible to program in SQL > it causes data-loss in query > (i mean unpredictable query result: > if you have two records (f=1,b=2),(f=1,b=3) > "distinct ON f" makes a value of b meaningless) > > So you MUST NOT select that way > > P.S. This situation means: you have wrong data structure. > > > ---(end of > broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to > [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] a wierd query
Are you sure about that Edmund? I have the following query: select distinct on (task_id, date) task_id, workhour_id, date from ( select task_id, workhour_id, begindate as date from workhour UNION select task_id, workhour_id, enddate as date from workhour )as dist1 which returns me 2763 rows in my case if I use the query without the top level select, like this: select task_id, workhour_id, begindate as date from workhour UNION select task_id, workhour_id, enddate as date from workhour I get 7146 rows. If I understand correctly there would be no need for the top level select if UNION would be to only return unique values. But given my test results this doesn't seem to be the case. Am I missing something or am I misinterpreting something? I mean I'm sure you get this information out of the documentation, that's why this question has risen. Kind regards, Stijn Vanroye > -Original Message- > From: Edmund Bacon [mailto:[EMAIL PROTECTED] > Sent: donderdag 13 mei 2004 17:28 > To: sad > Cc: [EMAIL PROTECTED] > Subject: Re: [SQL] a wierd query > > > sad wrote: > > select distinct a as F from table > > union > > select distinct b as F from table; > > > > Note that UNION only returns the unique values of the union > You can get repeated values by using UNION ALL. > > > -- > Edmund Bacon <[EMAIL PROTECTED]> > > ---(end of > broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] a wierd query
Sorry, I forgot one situation: if I run the query like so: select distinct on (task_id, begindate) task_id, workhour_id, begindate as date from workhour UNION select distinct on (task_id, enddate) task_id, workhour_id, enddate as date from workhour I get yet another value: 2961 rows. So I got 3 different result sets for 3 different ways to run the query. Even in this last case the UNION doesn't seem to only return unique values, and I will still need the top-level select. > -Original Message- > From: Stijn Vanroye > Sent: maandag 17 mei 2004 9:26 > To: [EMAIL PROTECTED] > Cc: 'Edmund Bacon' > Subject: RE: [SQL] a wierd query > > > Are you sure about that Edmund? > > I have the following query: > select distinct on (task_id, date) task_id, workhour_id, date from > ( > select task_id, workhour_id, begindate as date from workhour > UNION > select task_id, workhour_id, enddate as date from workhour > )as dist1 > which returns me 2763 rows in my case > > if I use the query without the top level select, like this: > select task_id, workhour_id, begindate as date from workhour > UNION > select task_id, workhour_id, enddate as date from workhour > I get 7146 rows. > > If I understand correctly there would be no need for the top > level select if UNION would be to only return unique values. > But given my test results this doesn't seem to be the case. > Am I missing something or am I misinterpreting something? I > mean I'm sure you get this information out of the > documentation, that's why this question has risen. > > > Kind regards, > > Stijn Vanroye > > > -Original Message- > > From: Edmund Bacon [mailto:[EMAIL PROTECTED] > > Sent: donderdag 13 mei 2004 17:28 > > To: sad > > Cc: [EMAIL PROTECTED] > > Subject: Re: [SQL] a wierd query > > > > > > sad wrote: > > > select distinct a as F from table > > > union > > > select distinct b as F from table; > > > > > > > Note that UNION only returns the unique values of the union > > You can get repeated values by using UNION ALL. > > > > > > -- > > Edmund Bacon <[EMAIL PROTECTED]> > > > > ---(end of > > broadcast)--- > > TIP 6: Have you searched our list archives? > > > >http://archives.postgresql.org > > > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] select distinct and order by
I solved it. Your first answer gave me a slap on the head (and by god I needed one :-) ). I simply used this: select distinct staff_id, fullname, loginname from staff where staff_id in (select staff_id from staffmembership where staffgroup_id=#someid#) order by fullname By omitting the on(staff_id) I believe it now distincts on all the fields. At first I was thinking that this would cause a problem, but it is not. Even if the fullname and the loginname are different the id will still seperate them. And a situation where the id is the same and the fullname and loginname are different is impossible. In your first option you use the word 'distinct' again, but this wil most certainly raise a parse error. The second option will work. It's not that complicated but two subselects for 3 fields from one and the same table may be a bit much though :-) Thanks for the help, Stijn Vanroye -Original Message- From: Viorel Dragomir [mailto:[EMAIL PROTECTED] Sent: woensdag 28 april 2004 11:54 To: Stijn Vanroye; [EMAIL PROTECTED] Subject: Re: [SQL] select distinct and order by I don't know if this will work, but why not try it ? :) select distinct fullname, distinct staff_id, loginname from staff where staff_id in (select staff_id from staffmembership where staffgroup_id=#SomeIdValue#) order by fullname, staff_id Next one is more complicate but with more chances to succeed :) select staff_id, fullname, loginname from ( select distinct on (staff_id) staff_id, fullname, loginname from staff where staff_id in (select staff_id from staffmembership where staffgroup_id=#SomeIdValue#) order by staff_id ) order by fullname Let me know :) - Original Message - From: Stijn Vanroye To: [EMAIL PROTECTED] Sent: Wednesday, April 28, 2004 12:27 Subject: [SQL] select distinct and order by Hello everybody, I have a short question: I'm trying to run the following query: select distinct on (staff_id) staff_id, fullname, loginname from staff where staff_id in (select staff_id from staffmembership where staffgroup_id=#SomeIdValue#) order by fullname I get the follwing error: select distinct on expressions must match the initial order by expression Does this mean that I can only order by the same fields as the ones that I use in the distinct? If so, is there still a way that I can select distinct on the keyfield, and still sort by the name. However unlikely, it could happen that two people have the same name, so a distinct on fullname could make problems. The facts: - PostgreSQL 7.3.2 running on RH 9 - pgODBC 7.3.0200 - Borland Delphi 7 enterprise Regards, Stijn Vanroye -=[Today I got more responsabilities from my boss, as from now I'm responsible for everything that goes wrong ...]=- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] select distinct and order by
Hello everybody, I have a short question: I'm trying to run the following query: select distinct on (staff_id) staff_id, fullname, loginname from staff where staff_id in (select staff_id from staffmembership where staffgroup_id=#SomeIdValue#) order by fullname I get the follwing error: select distinct on expressions must match the initial order by expression Does this mean that I can only order by the same fields as the ones that I use in the distinct? If so, is there still a way that I can select distinct on the keyfield, and still sort by the name. However unlikely, it could happen that two people have the same name, so a distinct on fullname could make problems. The facts: - PostgreSQL 7.3.2 running on RH 9 - pgODBC 7.3.0200 - Borland Delphi 7 enterprise Regards, Stijn Vanroye -=[Today I got more responsabilities from my boss, as from now I'm responsible for everything that goes wrong ...]=- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Order by YYYY MM DD in reverse chrono order trouble
Indeed, it seems that I get the same result for a similar query.
I'm running version 7.3.4 on a rh 9 server.
Also: is the function date_part a function you wrote yourself? I get an error stating
that the function date_part("Unknown",date) is not recognized.
It maybe not a solution to the actual problem but you could try this:
save the date and the time in two seperate fields. I use a similar construction for
convenience.
Regards,
Stijn.
> Hello,
>
> Hm, doesn't work for me:
>
> [EMAIL PROTECTED] mydb=> select distinct date_part('year', uu.add_date),
> date_part('month', uu.add_date), date_part('day',
> uu.add_date) from uus inner join ui on uus.user_id=ui.id
> inner join
> uu on ui.id=uu.user_id where uus.subscriber_user_id=1 order by
> uu.add_date desc;
>
> ERROR: For SELECT DISTINCT, ORDER BY expressions must appear
> in target
> list
>
> I have this version of PostgreSQL installed: postgresql-7.3.4-3.rhl9
>
> Thanks,
> Otis
>
>
> --- Tom Lane <[EMAIL PROTECTED]> wrote:
> > <[EMAIL PROTECTED]> writes:
> > > I'd love to be able to do that, but I cannot just ORDER BY
> > uu.add_date,
> > > because I do not have uu.add_date in the SELECT part of the
> > statement.
> >
> > Sure you can. Back around SQL89 there was a restriction that ORDER
> > BY
> > values had to appear in the SELECT list as well, but no modern
> > database
> > has such a restriction anymore ...
> >
> > regards, tom lane
>
>
> ---(end of
> broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faqs/FAQ.html
>
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] Order by YYYY MM DD in reverse chrono order trouble
Yes indeed, I seem to have misinterpreted that last one. My apologies.
The distinct solution I mentioned isn't going to solve it, you are absolutely right in
your example.
To get back on track:
You don't have to use a field in the select part of you query to be able to use it in
the order by clause. So using order by add_date should indeed work. Since it appears
that your add_date is a timestamp field (including time) it will order first on the
date part, and next on the time part.
E.g.:
2004-04-12 12:45:22
2004-04-12 09:55:25
2004-04-11 14:25:31
2004-04-11 11:11:25
Since you have a distinct only on the date parts of the timestamp this will make no
difference in the end result, it will still be sorted correctly on the date.
but I'm wondering if a GROUP BY wouldn't also be a good solution? like this:
<---CODE>
SELECT
date_part('year', uu.add_date), date_part('month', uu.add_date),
date_part('day', uu.add_date)
FROM uus INNER JOIN ui ON uus.user_id=ui.id INNER JOIN uu ON
ui.id=uu.user_id
WHERE uus.x_id=1
GROUP BY
date_part('year', uu.add_date), date_part('month', uu.add_date),
date_part('day', uu.add_date);
<---END CODE>
That way you don't need the DISTINCT part. The disadvantage however is that all the
fields in the select must eighter be a part of the GROUP BY clause, or be used in an
aggregate function.
Anyways: just descide whatever solution fits best for your needs.
Regards,
Stijn.
>Otis wrote:
> Hello,
>
> But will this work even with my add_date column, which is a TIMESTAMP
> field? Values in this column contain hours, minutes,
> seconds, etc., so
> won't DISTINCT return multiple rows for add_dates that _are_ distinct,
> but are on the same day.
>
> For example:
>
> 2004 04 02 11:22:33.034
> 2004 04 02 22:33:44.055
>
> Thanks,
> Otis
>
>
> --- Stijn Vanroye <[EMAIL PROTECTED]> wrote:
> > > Hello,
> > >
> > > I'd love to be able to do that, but I cannot just ORDER BY
> > > uu.add_date,
> > > because I do not have uu.add_date in the SELECT part of the
> > > statement.
> > > The reason I don't have it there is because I need
> distinct MM
> > DD
> > > values back.
> > > Is there a trick that I could use to make this more elegant?
> > yes, you could use:
> > SELECT DISTINCT ON (field1, field2) field1, field3, FieldN
> from table
> >
> > Regards,
> >
> >
> > Stijn Vanroye
>
>
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Order by YYYY MM DD in reverse chrono order trouble
> Hello,
>
> I am trying to select distinct dates and order them in the reverse
> chronological order. Although the column type is TIMESTAMP, in this
> case I want only , MM, and DD back.
>
> I am using the following query, but it's not returning dates back in
> the reverse chronological order:
>
> SELECT DISTINCT
> date_part('year', uu.add_date), date_part('month', uu.add_date),
> date_part('day', uu.add_date)
>
> FROM uus INNER JOIN ui ON uus.user_id=ui.id INNER JOIN uu ON
> ui.id=uu.user_id
> WHERE uus.x_id=1
>
> ORDER BY
> date_part('year', uu.add_date), date_part('month', uu.add_date),
> date_part('day', uu.add_date) DESC;
>
>
> This is what the above query returns:
>
> date_part | date_part | date_part
> ---+---+---
> 2004 | 2 | 6
> 2004 | 4 |20
> (2 rows)
>
>
> I am trying to get back something like this:
> 2004 4 20
> 2004 4 19
> 2004 2 6
> ...
>
> My query is obviously wrong, but I can't see the mistake. I was
> wondering if anyone else can see it. Just changing DESC to ASC, did
> not work.
>
> Thank you!
> Otis
What you could try to do in your order by clause is the following:
ORDER BY
date_part('year', uu.add_date) DESC,
date_part('month', uu.add_date) DESC,
date_part('day', uu.add_date) DESC;
That way you are sure each of the fields is sorted DESC. if you don't specify a
direction in your order by clause postgres will take ASC as the default. I think that
he does "ASC,ASC,DESC" instead. I'm not sure if he applies the DESC to all specified
fields in the order by if you declare it only once.
Regards,
Stijn Vanroye
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] three-way join - solved
First off, I'm sorry I couldn't get you a reply sooner. I see you have found another way, which is good :-) > I've sorted it. > > Firstly, I've done away with the status field. If an item's > been issued or is > still on order it does not have a location. I've therefore > set up two > locations, one of 'On Order' and one of 'Issued'. That's got > rid of one > table/relationship. It's also possible to include a status field in the consumables table, so you can give each consumable item it's own status directly. That way you won't have to perform a join operation to get the status. And/or you can filter directly on the consumables table, wich I think can have a performance benefit (no join nescessary). Neighter do you need two location that aren't actually locations, which makes it easier to generate a list of (real) locations if nescescary. /*status field doesn't have to be a relation but a field which can only contain 3 values (your statusses)).*/ But hey, your solution is just as good, you have to look at the situation at hand. > > The relationship between the stock and the locations is > simple. Each stock > item has a location. I therefore do a straight forward join > to end up with > stock+location information. > > The Stock->Consumable relation is just as simple. I do a > straight forward > join of the new stock+location data with the consumables > data, and end up > with what I need, consumable, location and quantity details, i.e. > > create view stock as >select c.*, b.cost_cl_id, b.cl_desc, b.qty from consumables c, > (select b.*, cl.cl_desc > from balances b, cons_locations cl > where b.cost_cl_id = cl.cl_id > order by cost_cs_id) b >where c.cs_id = b.cost_cs_id; > > -- > Gary Stainburn For what it's worth here's a query that I think might work fine in you original situation, using your already created view: select bmain.cost_cs_id, consumables.cs_make, consumables.cs_comments, cons_locations.cl_desc, b1.qty as hand_qty, b2.qty as order_qty from (select cost_cs_id, cost_cl_id from balances group by cost_cl_id, cost_cs_id) as bmain left join (select qty, cost_cs_id, cost_cl_id from balances where cost_css_id=1) as b1 on (bmain.cost_cl_id=b1.cost_cl_id and bmain.cost_cs_id=b1.cost_cs_id) left join (select qty, cost_cs_id, cost_cl_id from balances where cost_css_id=2) as b2 on (bmain.cost_cl_id=b2.cost_cl_id and bmain.cost_cs_id=b2.cost_cs_id) left join consumables on (bmain.cost_cs_id=consumables.cs_id) left join cons_locations on (bmain.cost_cl_id=cons_locations.cl_id) P.S. Don't ask about performance of the query, I haven't delved that deep into it :-) Regards and good luck, Stijn Vanroye ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] three-way join
Gary wrote: > Hi folks, > > here's a straight forward join that I simply can't get my head round. > > I've got > > consumables: cs_id, cs_make, cs_comments > cons_locations: cl_id, cl_desc > cons_stock: cs_id, cl_id, status (1=ordered, 2=in-stock) > > (one stock record per stock item, qty=3 means 3 records) assuming that the PK's are: consumables : cs_id cons_loacations: cl_id cons_stock: cs_id, cl_id You could only have 1 record in cons_stock for each unique combination of consumable and location. If the primary key for cons_stock would also include the field status you could have 2 records for each unique combination of consumable and location, one where status is ordered, and one where status is in-stock. > I'm struggling to create a quiery to produce > > cs_id, cs_make, cs_comments, cl_desc, hand_qty, order_qty > > where hand_qty and order_qty is the number of records grouped > by cs_id, cl_id, > and status. Given the previous, the result for qty would be pretty obvious I think, since you would have only 1 record for the combination cs_id,cl_id and status. > > I've done the simple part and created a view balances to > tally the cons_stock > as: > > create view balances as > select cost_cs_id, cost_cl_id, cost_css_id, count(*) as qty > from cons_stock > group by cost_cs_id, cost_cl_id, cost_css_id; I don't understand where the cost_* fields come from, especially the cost_css_id field. Assuming that these fields are the cs_id, cl_id and status qty is most likley going to be 1 all the time? Maybe it's worth to rethink your database structure, or adding the qty fields to the table cons_stock and keeping them up-to-date? (eg. CONS_STOCK (cs_id, cl_id, hand_qty, order_qty) PK(cs_id, cl_id) ) that way you simply change the quantity fields for each combination of location-consumable according to the situation (and sound the alarm if the reach a certain level?). If anyone thinks I'm wrong, please correct me. Regards, Stijn Vanroye ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
