Re: [SQL] Group by on Date

In article <1318661510830-4904685.p...@n5.nabble.com>, "maya.more" writes: > I have a table with Date and unit column. . I want to find sum of unit column > considering 3 days each > User will specify start and enddate > Eg > DateUnit > 10/1/2011 1 > 10/2/2011 2 > 10/3/201

Re: [SQL] Group by on Date

maya.more wrote: > I have a table with Date and unit column. . I want to find sum of unit column > considering 3 days each > > User will specify start and enddate > > Eg > > DateUnit > 10/1/2011 1 > 10/2/2011 2 > 10/3/2011 3 > 10/4/2011 4 > 10/5/2011 4 > 10/6/

[SQL] Group by on Date

I have a table with Date and unit column. . I want to find sum of unit column considering 3 days each User will specify start and enddate Eg DateUnit 10/1/2011 1 10/2/2011 2 10/3/2011 3 10/4/2011 4 10/5/2011 4 10/6/2011 1 10/7/2011 2 10/8/2011 3 10/

Re: [SQL] group by with sum and sum till max date

On Tue, Jul 5, 2011 at 10:42 AM, M. D. wrote: > This is a little hard to explain, and I'm not sure if it's possible, but > here goes. > > This is my query: > select year, month, > (select number from account where account.account_id = > view_account_change.account_**id) as number, > (select name

[SQL] group by with sum and sum till max date

This is a little hard to explain, and I'm not sure if it's possible, but here goes. This is my query: select year, month, (select number from account where account.account_id = view_account_change.account_id) as number, (select name from account where account.account_id = view_account_change.a

Re: [SQL] group by hour + distinct

Michele Petrazzo - Unipex wrote: P.s. Have you some references about the "subquery" keyword? I found only the word subquery as "use" (for example: select a from b where id in (select id from table)), but not as sql [sic] command. "subquery" is not an SQL keyword.

Re: [SQL] group by hour + distinct

Ciao * Olá! for each hour it will count the number of distinct user_id's there are . If I understood correctly what you need... Can you please test it and see if it is OK for your needs? With me, it worked on the sample data you provided Yes! Simply perfect! * Great to hear it worked

Re: [SQL] group by hour + distinct

Oliveiros d'Azevedo Cristina ha scritto: Howdy, Michelle, Ciao for each hour it will count the number of distinct user_id's there are . If I understood correctly what you need... Can you please test it and see if it is OK for your needs? With me, it worked on the sample data you provided

Re: [SQL] group by hour + distinct

1:39 AM Subject: [SQL] group by hour + distinct Hi list, Into a table like this: id_user | my_date and some data inside 1 | 2010/11/25 00:01:00 1 | 2010/11/25 00:02:00 1 | 2010/11/25 01:01:00 2 | 2010/11/25 02:01:00 3 | 2010/11/25 02:01:00 3 | 2010/11/25 02:06:00 1 | 2010/11/25 03:01:00 I'm

[SQL] group by hour + distinct

Hi list, Into a table like this: id_user | my_date and some data inside 1 | 2010/11/25 00:01:00 1 | 2010/11/25 00:02:00 1 | 2010/11/25 01:01:00 2 | 2010/11/25 02:01:00 3 | 2010/11/25 02:01:00 3 | 2010/11/25 02:06:00 1 | 2010/11/25 03:01:00 I'm looking for a query that say me, hour per hour, how

Re: [SQL] GROUP BY on a column which might exist in one of two tables

On Wed, 2008-06-25 at 14:20 +, Greg Sabino Mullane wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: RIPEMD160 > > > > Where the totals are "counts" of the the rows in the hits and views > > tables. There should be no rows for partners without hits or views. > > How about something like th

Re: [SQL] GROUP BY on a column which might exist in one of two tables

-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > Where the totals are "counts" of the the rows in the hits and views > tables. There should be no rows for partners without hits or views. How about something like this?: SELECT partner_id, COUNT(view_id) AS total_views, COUNT(hit_id) AS tota

Re: [SQL] GROUP BY on a column which might exist in one of two tables

> select > coalesce(h.partner_id, v.partner_id) as partner_id, > coalesce(v.count, 0) as total_views, > coalesce(h.count, 0) as total_hits > from > (select partner_id, count(*) from hits group by partner_id) as h > full outer join > (select partner_id, count(*) from views g

Re: [SQL] GROUP BY on a column which might exist in one of two tables

On Wed, Jun 25, 2008 at 09:50:18AM -0400, Mark Stosberg wrote: > hits > hit_id > partner_id > > views > view_id > partner_id > > There is of course a "partners" table with a "partner_id" column. > > My target result is more like > > partner_id > total_views > total_hits select co

[SQL] GROUP BY on a column which might exist in one of two tables

Hello, I could use some help figuring out a complex "GROUP BY". As a setup, let's say I have two tables which stores a single "hit" or "view" in a row hits hit_id partner_id views view_id partner_id There is of course a "partners" table with a "partner_id" column. My target result is

Re: [SQL] group by range of values

2007/7/27, Carol Cheung <[EMAIL PROTECTED]>: db=# select * from tester order by birth_year; birth_year | salary + 1946 | 78000 1949 | 61000 What is the data type of the birth_year column? I'd suggest using date if you can, as what it is is a date with

Re: [SQL] group by range of values

On 7/27/07, I wrote: > On 7/27/07, Carol Cheung <[EMAIL PROTECTED]> wrote: > > Something like: > > > > decade | average(salary) > > ---+- > >1940 | 69500 > >1950 | 5.33 > >1960 | 53000 > >1970 | 40333.33 > > CREATE TABLE tester ( > birth_year integer, >

Re: [SQL] group by range of values

Hello you can use CASE like SELECT CASE WHEN birth_year BETWEEN 1940 AND 1949 THEN 1940 WHEN birth_year BETWEEN 1950 AND 1959 THEN 1950 WHEN birth_year BETWEEN 1960 AND 1969 THEN 1960 WHEN birth_year BETWEEN 1970 AND 1979 THEN 1979 END, AVG(salary) FROM tester GROUP BY 1 ORDER BY 1; R

Re: [SQL] group by range of values

On 7/27/07, Carol Cheung <[EMAIL PROTECTED]> wrote: > Something like: > > decade | average(salary) > ---+- >1940 | 69500 >1950 | 5.33 >1960 | 53000 >1970 | 40333.33 CREATE TABLE tester ( birth_year integer, salary numeric(10,2) ); SELECT SUBSTRING(T

Re: [SQL] group by range of values

Carol Cheung wrote: Hello, Here's my table: db=# select * from tester order by birth_year; birth_year | salary + 1946 | 78000 1949 | 61000 1951 | 58000 1953 | 56000 1958 | 52000 1962 | 5 1965 | 45000 1967 |

[SQL] group by range of values

Hello, Here's my table: db=# select * from tester order by birth_year; birth_year | salary + 1946 | 78000 1949 | 61000 1951 | 58000 1953 | 56000 1958 | 52000 1962 | 5 1965 | 45000 1967 | 6 1968 |

Re: [SQL] group by day

Edward W. Rouse wrote: Oh, postgres 7.4 by the way. The latest release there is 7.4.17 - make sure you're running that revision. See the website for what bugs have been fixed. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7:

Re: [SQL] group by day

On 5/24/07, Edward W. Rouse <[EMAIL PROTECTED]> wrote: I have an audit table that I am trying to get a count of the number of distinct entries per day by the external table key field. I can do a select count(distinct(id)) from audit where timestamp >= '01-may-2007' and get a total count. What

Re: [SQL] group by day

am Thu, dem 24.05.2007, um 14:49:47 -0400 mailte Edward W. Rouse folgendes: > I have an audit table that I am trying to get a count of the number of > distinct > entries per day by the external table key field. I can do a > > select count(distinct(id)) from audit where timestamp >= '01-may-2007

[SQL] group by day

I have an audit table that I am trying to get a count of the number of distinct entries per day by the external table key field. I can do a select count(distinct(id)) from audit where timestamp >= '01-may-2007' and get a total count. What I need is a way to group on each day and get a count

Re: [SQL] Group by minute

On Fri, 2006-09-22 at 10:52 -0300, Ezequias Rodrigues da Rocha wrote: > Hil list, > > I have a query but my IDE (Delphi) does not accept "to_char" > capability. Is there a way to reproduce the same query without using > to_char function ? > > Here is my query: > SELECT to_char(quando,'dd/MM/

Re: [SQL] Group by minute

Ezequias Rodrigues da Rocha wrote: Hil list, I have a query but my IDE (Delphi) does not accept "to_char" capability. Is there a way to reproduce the same query without using to_char function ? Here is my query: SELECT  to_char(quando,'dd/MM/ HH24:MI'),count(id) FROM base.tentativa

Re: [SQL] Group by minute

On 22 sep 2006, at 15.52, Ezequias Rodrigues da Rocha wrote: I have a query but my IDE (Delphi) does not accept "to_char" capability. Is there a way to reproduce the same query without using to_char function ? ... GROUP BY to_char(quando,'dd/MM/ HH24:MI') To group by minute, you can t

[SQL] Group by minute

Hil list,I have a query but my IDE (Delphi) does not accept "to_char" capability. Is there a way to reproduce the same query without using to_char function ?Here is my query:SELECT  to_char(quando,'dd/MM/ HH24:MI'),count(id) FROM base.tentativaWHERE  (SESSAO_ID = 15) GROUP BY to_char(quando,'dd

[SQL] GROUP BY issue

Hy list. I have 3 tables: performance event pool_performance each event has it's performances, and can be assigned to one or more pools. pool_performance holds performance_id, pool_id and from_date performance has event_id and performance_id as primary key what I want to do is select min(p

Re: [SQL] group by function, make SQL cleaner?

Bryce Nesbitt wrote: > Tom Lane wrote: >> In this particular case you could say >> >> ... GROUP BY 1 ORDER BY 1; >> >> "ORDER BY n" as a reference to the n'th SELECT output column is in the >> SQL92 spec. (IIRC they removed it in SQL99, but we still support it, >> and I think most other DBMSe

Re: [SQL] group by function, make SQL cleaner?

this should work, # SELECT date_trunc('day',endtime),count(*) FROM eg_event where endtime >= '2006-02-01' and endtime < '2006-03-01' GROUP BY 1 ORDER BY 1; best regards, Stefan Am Donnerstag, 16. März 2006 06:18 schrieb Bryce Nesbitt: > I've got a working query: > > stage=# SELEC

Re: [SQL] group by function, make SQL cleaner?

this should work, # SELECT date_trunc('day',endtime),count(*) FROM eg_event where endtime >= '2006-02-01' and endtime < '2006-03-01' GROUP BY 1 ORDER BY 1; hope this helps best regards, Stefan Am Donnerstag, 16. März 2006 06:18 schrieb Bryce Nesbitt: > I've got a working query: >

Re: [SQL] group by function, make SQL cleaner?

Tom Lane wrote: In this particular case you could say ... GROUP BY 1 ORDER BY 1; "ORDER BY n" as a reference to the n'th SELECT output column is in the SQL92 spec. (IIRC they removed it in SQL99, but we still support it, and I think most other DBMSes do too.) "GROUP BY n" is *not* in an

Re: [SQL] group by function, make SQL cleaner?

Bryce Nesbitt <[EMAIL PROTECTED]> writes: > SELECT date_trunc('day',endtime),count(*) > FROM eg_event where endtime >= '2006-02-01' and endtime < '2006-03-01' > GROUP BY date_trunc('day',endtime) > ORDER BY date_trunc('day',endtime); > Is there a way to eliminate the ugly repeated use of > date_t

[SQL] group by function, make SQL cleaner?

I've got a working query: stage=# SELECT date_trunc('day',endtime),count(*) FROM eg_event where endtime >= '2006-02-01' and endtime < '2006-03-01' GROUP BY date_trunc('day',endtime) ORDER BY date_trunc('day',endtime); date_trunc | count -+--- 2006-02-01 00:00:0

Re: [SQL] group by complications

Hi, Mark, Mark Fenbers schrieb: > Wow! I didn't know you could have a (select ...) as a replacement for a > 'from' table/query. Your SQL worked as-is, except I had to add a 'limit > 1' to the first subquery. > > Thanks! I would have never figured that out on my own! SQL has more power than mo

Re: [SQL] group by complications

Wow!  I didn't know you could have a (select ...) as a replacement for a 'from' table/query.  Your SQL worked as-is, except I had to add a 'limit 1' to the first subquery. Thanks!  I would have never figured that out on my own! Mark chester c young wrote: --- Mark Fenbers <[EMAIL PROTECTE

Re: [SQL] group by complications

--- Mark Fenbers <[EMAIL PROTECTED]> wrote: > select l.lid,l.fs,max(h.obstime) from location as l > inner join height as h on h.lid = l.lid > where l.fs > 0.0 > group by l.lid,l.fs; > > The above query works as expected in that is fetches the lid, fs and > time of the latest observation in the h

[SQL] group by complications

select l.lid,l.fs,max(h.obstime) from location as l inner join height as h on h.lid = l.lid where l.fs > 0.0 group by l.lid,l.fs; The above query works as expected in that is fetches the lid, fs and time of the latest observation in the height table (for the corresponding lid), but I also want

Re: [SQL] Group by 15 Minute Steps

# Usally, the steps between each entries is 300 seconds. How can I # group by by 15, 30, 45 minutes so that i can get averages over the # specified timeframe? For 15-minute data, I'd compute the "quadrant" of each record and group by the quadrant number. Anything that occurs from :00 to :14 is i

[SQL] Group by 15 Minute Steps

Hi, does anyone hava an idea how to group data e.g by 15 minute steps? I have the following data in my "data_diff" table [...] snmp=# \d data_diff Table "public.data_diff" Column | Type | Modifiers -+-+---

Re: [SQL] group by before and after date

I have 2 tables 1 has a date field and component need by that date and the other has all the upcoming orders. I am trying to build a query that will give me the Date and ComponentNeed and also how many components have been ordered before that date and how many after. PostGreSQL is telling me I

[SQL] group by before and after date

I have 2 tables 1 has a date field and component need by that date and the other has all the upcoming orders. I am trying to build a query that will give me the Date and ComponentNeed and also how many components have been ordered before that date and how many after. PostGreSQL is telling me I need

Re: [SQL] group by before and after date

I got it. I had to put the whole case statement into the sum so my statement ended up: select a.DueDate,a.PartID,a.AmountNeeded,a.CurrentStock, sum(coalesce(case when b.DatePromisedBy<=a.DueDate then coalesce(b.QuantityOrdered,0)-coalesce(b.DeliveredSum,0) end,0)) as ExpectedBefore, sum(coale

Re: [SQL] Group by and aggregates

Michael L. Hostbaek wrote: >Now, I need the first line to say "15.00" in the cmup field. That is, >stock and incoming are obviously not being grouped, but since it's the >same partno I'd like somehow to show the highest cmup. Is there some >black SQL voodoo that'll achieve this ? I think you need

Re: [SQL] Group by and aggregates

Franco Bruno Borghesi (franco) writes: > If I understand well, you want the highest cmup for each partno, that is > max(cmup) grouped by partno (only). > > SELECT T.partno, T.status, TMP.max_cmup_for_partno, max(T.cmup) AS > max_cmup, sum(T.qty) AS sum_qty > FROM my_table T, (SELECT partno,

Re: [SQL] Group by and aggregates

On Thu, 2004-11-04 at 16:54, Michael L. Hostbaek wrote: ... > some_id partno status cmupqty > 1 test1 stock 10.00 15 > 2 test2 incoming12.00 10 > 3 test1 incoming15.00 60 > 4 test1 incoming14.00 11 ... > My result

Re: [SQL] Group by and aggregates

On Thu, Nov 04, 2004 at 05:54:30PM +0100, Michael L. Hostbaek wrote: > some_id partno status cmupqty > 1 test1 stock 10.00 15 > 2 test2 incoming12.00 10 > 3 test1 incoming15.00 60 > 4 test1 incoming14.00 11 >

Re: [SQL] Group by and aggregates

If I understand well, you want the highest cmup for each partno, that is max(cmup) grouped by partno (only). You can achieve this with a subselect, and then you join the results whith the query you already have: SELECT T.partno, T.status, TMP.max_cmup_for_partno, max(T.cmup) AS max_cmup, sum

Re: [SQL] Group by and aggregates

Michael L. Hostbaek wrote: List, I've got a table looking something like this: my_table some_id int bla bla, partno varchar(100), status varchar(100), cmup numeric(14,2), qty int My SQL select statement will then group together partno, status and aggregate

[SQL] Group by and aggregates

List, I've got a table looking something like this: my_table some_id int bla bla, partno varchar(100), status varchar(100), cmup numeric(14,2), qty int Here a small sample of contents in my table: some_id partno status cmupqty 1 test1

Re: [SQL] Group by failing on Null values

while you weren't looking, Caleb Simonyi-Gindele <[EMAIL PROTECTED]> wrote: > How do I get it to produce a row even when dat_staff_code is null? try something like SELECT coalesce(dat_staff_code, 0)...GROUP BY dat_staff_code That will substitute a zero for any NULL value in that column, which G

[SQL] Group by failing on Null values

I have a SELECT query that basically adds up my sales, removes credit adjustments (eg. Returns) and gives the net figure.   This is the query: SELECT (SELECT SUM(cli_tran_amount) FROM vetpmardet WHERE cli_credit_adj_trans_no IN (SELECT sys_tran_number from vetpmardet WHERE cli_tran_trans

Re: [SQL] group by not returning sorted rows

On Mon, 2004-04-05 at 17:34, Tom Lane wrote: > Bret Hughes <[EMAIL PROTECTED]> writes: > > and the rows resulting from the query are no longer sorted by log date. > > Is this a change since 7.2x? > > Yes. 7.4 can use hashing instead of sorting to bring grouped rows > together. > > > I can achie

Re: [SQL] group by not returning sorted rows

Bret Hughes <[EMAIL PROTECTED]> writes: > and the rows resulting from the query are no longer sorted by log date. > Is this a change since 7.2x? Yes. 7.4 can use hashing instead of sorting to bring grouped rows together. > I can achieve the results I need by adding an order by clause identical

Re: [SQL] group by not returning sorted rows

On Mon, 5 Apr 2004, Bret Hughes wrote: > select cities.name as city, buildings.name as building, > pagename, > log_date , > sum(exhibition_count) as tot > from logrecords > join cities on (logrecords.city=cities.num) > join buildings on (logrecords.building=buildings.

[SQL] group by not returning sorted rows

I have a query: select cities.name as city, buildings.name as building, pagename, log_date , sum(exhibition_count) as tot from logrecords join cities on (logrecords.city=cities.num) join buildings on (logrecords.building=buildings.num) where adver

Re: [SQL] Group By Error Text

Joe, > ERROR: Attribute drawing_register.jobno must be GROUPed or used in an > aggregate function > > Is this correct? Getting rid of the error is easy: > > select jobno, count(jobno) from drawing_register group by jobno; > > I believe that jobno is being used in an aggregate function. Should

[SQL] Group By Error Text

Hi with the following query: select jobno, count(jobno) from drawing_register; I get the following error: ERROR: Attribute drawing_register.jobno must be GROUPed or used in an aggregate function Is this correct? Getting rid of the error is easy: select jobno, count(jobno) from drawing_regist

Re: [SQL] sql group by statement

On Fri, 13 Sep 2002 12:29:21 +0200, "Albrecht Berger" <[EMAIL PROTECTED]> wrote: >Table : >pk id val1 val2 > 112 3 > 212 4 > 321 1 > 410 5 > 521 8 > > >Needed Result : >pk id val1 val2 > 410 5 > 521 8 Albrecht, "DISTI

Re: [SQL] sql group by statement

but how do I know that "distinct on" doesn't cut off the row with max(val2) of that id that I need ? > see the yesterday's thread about DISTINCT ON (non-standard Postgres feature) > > > I have a problem, which I'm not able to solve with a simple query : > > > > I need a resultset with distinct i

Re: [SQL] sql group by statement

see the yesterday's thread about DISTINCT ON (non-standard Postgres feature) > I have a problem, which I'm not able to solve with a simple query : > > I need a resultset with distinct id's, but the max val2 of each id. > I tried to group by id, but though I need the pk in my resultset > I have t

[SQL] sql group by statement

Hello, I have a problem, which I'm not able to solve with a simple query : I need a resultset with distinct id's, but the max val2 of each id. I tried to group by id, but though I need the pk in my resultset I have to group it too, which "destroys" the group of val2. Can this be done without a h

Re: [SQL] group by weirdness

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

Re: [SQL] group by weirdness

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

Re: [SQL] group by weirdness

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;

Re: [SQL] group by weirdness

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 >

Re: [SQL] group by weirdness

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

Re: [SQL] group by weirdness

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

[SQL] group by weirdness

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

Re: [SQL] Group by date_part

From: "Roberto Mello" <[EMAIL PROTECTED]> > On Tue, Jul 10, 2001 at 08:04:55PM +0100, Graham Vickrage wrote: > > > > The statement I have only selects the count if there is at least 1 order for > > a particular day, which make sense. > > > > I however need a count of 0 for days that don't have an

Re: [SQL] Group by date_part

On Tue, Jul 10, 2001 at 08:04:55PM +0100, Graham Vickrage wrote: > > The statement I have only selects the count if there is at least 1 order for > a particular day, which make sense. > > I however need a count of 0 for days that don't have any. Can anyone help? > > SQL: > > SELECT date_part('

[SQL] Group by date_part

Hi, I need to select the amount of orders per day from an order table. The statement I have only selects the count if there is at least 1 order for a particular day, which make sense. I however need a count of 0 for days that don't have any. Can anyone help? SQL: SELECT date_part('day', date)

[SQL] group by: properly?

Hello, I must confess I have always been a bit baffled by the GROUP BY, therefore I would appreciate if you could tell me if there's a better way: I have the table "items": id | integer | not null default nextval('items_id_seq'::text) seller_id| integer | not null materi

Re: [SQL] Group by within table joins

Hey, If you use group then except aggreate functions(count,sum..) other items in your select list should be in your group list also. Bernie Huang wrote: > Hi, I have the following SQL: > > SELECT ltb.v_id, >count(ltb.v_id) AS num_of_times_borrowed, >vtb.equip_attr[1] AS year, >

Re: [SQL] Group by within table joins

Mr. Huang, Seems to me that your GROUP BY line should read: GROUP BY ltb.v_id, vtb.equip_attr[1], vtb.equip_attr[3], vtb.equip_attr[4] Or am I missing the point? -Josh > SELECT ltb.v_id, >count(ltb.v_id) AS num_of_times_borrowed, >vtb.equip_attr

[SQL] Group by within table joins

Hi, I have the following SQL: SELECT ltb.v_id, count(ltb.v_id) AS num_of_times_borrowed, vtb.equip_attr[1] AS year, vtb.equip_attr[3] AS model, vtb.equip_attr[4] AS type FROM log_tb ltb, vehicle_tb vtb WHERE ltb.v_id=vtb.equip_id GROUP BY ltb.v_id ORDER BY year; "ERRO

Re: [SQL] GROUP by finish&&last day of month

Antti Linno wrote: > Ok, if I want to get non-aggregat data in groups, I use order by. This > group by seemed so logical though, but the fruit that u can't have, is > usually the most sweet. > > New question, how to get the last day of month(order data by last day of > month). And to prevent chai

Re: [SQL] GROUP by finish&&last day of month

Not quite sure what you mean, but how about this: select date_part('day', 'Jul 01 00:00:00 2000 PDT'::datetime-1); You must specify Jul for June, i.e. always the following month. You could get around that too, but since I don't know why you need to do what you asked, I'll leave it at this. Tr

Re: [SQL] GROUP by finish&&last day of month

On Wed, 5 Jul 2000, Antti Linno wrote: > New question, how to get the last day of month(order data by last day of > month). I'm sorry but I don't understand how you could order something by a value (last day of month). you can only order things by a field (e.g. a date field) or fields, in incr

[SQL] GROUP by finish&&last day of month

Ok, if I want to get non-aggregat data in groups, I use order by. This group by seemed so logical though, but the fruit that u can't have, is usually the most sweet. New question, how to get the last day of month(order data by last day of month). And to prevent chain letter from misunderstanding

Re: [SQL] Group BY ...(cont.)

No, you don't you could say: SELECT workgroup, id FROM job GROUP BY workgroups; On Wed, 5 Jul 2000, Antti Linno wrote: > Ok, if I want to use group by, then I have to put every select field > afterwards to group by. But I do want to group by one field. > Hence > select workgroup,id from job gro

Re: [SQL] Group BY ...(cont.)

E.g. select workgroup from job group by workgroup; will that do? Troy > > Ok, if I want to use group by, then I have to put every select field > afterwards to group by. But I do want to group by one field. > Hence > select workgroup,id from job group by workgroup,id; > would create groups o

Re: [SQL] GROUP by

You can use group by without having an aggregate operator but if you use an aggregate you have to put the group by column int the select. By the way what is after the AND? I hope it is not another workgroup condition because it doesn't have much sense, I think. If you are filtering different type

Antw: [SQL] GROUP by

Antti Linno <[EMAIL PROTECTED]> wrote: > I have table job, and I want to select data from it, grouping by > workgroups. When I use > select id,name from job where workgroup='top leaders' AND ... group by > workgroup; > When I want to group, I get > ERROR: Illegal use of aggregates or non-gr

[SQL] Group BY ...(cont.)

Ok, if I want to use group by, then I have to put every select field afterwards to group by. But I do want to group by one field. Hence select workgroup,id from job group by workgroup,id; would create groups of 1? But I want to group by workgroups. Sorry if my explanation is fuzzy. A.

Re: [SQL] GROUP by

On Wed, 5 Jul 2000, Antti Linno wrote: > I have table job, and I want to select data from it, grouping by > workgroups. When I use > select id,name from job where workgroup='top leaders' AND ... group by > workgroup; this query is probably incorrect: because of the "where workgroup=" the "gro

[SQL] GROUP by

I have table job, and I want to select data from it, grouping by workgroups. When I use select id,name from job where workgroup='top leaders' AND ... group by workgroup; When I want to group, I get ERROR: Illegal use of aggregates or non-group column in target list I'm confused, why does quer