From: | "John Gilson" <jag(at)acm(dot)org> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: HELP w/ SQL -- distinct select with non distinct fields? |
Date: | 2002-09-23 23:46:14 |
Message-ID: | aJNj9.12567$R8.3578139@twister.nyc.rr.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
"RVL" <rlyudmirsky(at)linkonline(dot)net> wrote in message
news:c5c42943(dot)0209231423(dot)3d143db6(at)posting(dot)google(dot)com(dot)(dot)(dot)
> I'm work with Sybase on the Sun... and, being a clueles newbee in SQL
> department, hope you could help. I have a set of data:
>
> acct name qty link date memo
> 101 item_A 100 0001 9/2/02 blah
> 101 item_A 250 0001 9/3/02 n/a
> 101 item_A 80 0002 9/3/02 n/a
> 101 item_B 90 0002 8/8/02 n/a
> 101 item_B 120 0003 9/7/02 n/a
> 101 item_B 100 0003 9/2/02 abcd
> 102 item_B 100 0004 9/3/02 xyz
> 102 item_B 100 0004 9/7/02 xyz
> 102 item_C 15 0005 9/1/02 n/a
> 102 item_C 180 0005 9/5/02 n/a
>
> I need it to be consolidated by [link] and sorted by [acct] [name] and
> subtotaled by [qty]. This is easy if I don't use date and memo:
> SELECT DISTINCT acct, name, sum(qty), link FROM item_list
> GROUP BY acct, name, link ORDER BY acct, name, line
>
> acct name qty link
> 101 item_A 350 0001
> 101 item_A 170 0002
> 101 item_B 220 0003
> 102 item_B 200 0004
> 102 item_C 195 0005
>
> However, I want [date] and [memo] from the _first_ record of the group
> to be included.
>
> acct name qty link date memo
> 101 item_A 350 0001 9/2/02 blah
> 101 item_A 170 0002 9/3/02 n/a
> 101 item_B 220 0003 8/8/02 n/a
> 102 item_B 200 0004 9/3/02 xyz
> 102 item_C 195 0005 9/1/02 n/a
>
> Fields [date] and [memo] are not diplicates, so I cannot consolidate
> the set if I add them to SELECT. Is there another way to solve this?
It helps to supply a CREATE TABLE and an INSERT so that the
problem is better defined and a proposed solution can be easily tested.
DATE is reserved in SQL so I'll change the column name to the less
readable "d". Also, I believe your result is incorrect.
CREATE TABLE item_list
(
acct INT NOT NULL,
name VARCHAR(10) NOT NULL,
qty INT NOT NULL,
link VARCHAR(5) NOT NULL,
d DATETIME NOT NULL,
memo VARCHAR(10) NOT NULL,
PRIMARY KEY (acct, name, link, d)
)
INSERT INTO item_list
VALUES (101, ' item_A', 100, '0001', '20020902', 'blah')
INSERT INTO item_list
VALUES (101, 'item_A', 250, '0001', '20020903', 'n/a')
INSERT INTO item_list
VALUES (101, 'item_A', 80 , '0002', '20020903', 'n/a')
INSERT INTO item_list
VALUES (101, 'item_B', 90, '0002', '20020808', 'n/a')
INSERT INTO item_list
VALUES (101, 'item_B', 120, '0003', '20020907', 'n/a')
INSERT INTO item_list
VALUES (101, 'item_B', 100, '0003', '20020902', 'abcd')
INSERT INTO item_list
VALUES (102, 'item_B', 100, '0004', '20020903', 'xyz')
INSERT INTO item_list
VALUES (102, 'item_B', 100, '0004', '20020907', 'xyz')
INSERT INTO item_list
VALUES (102, 'item_C', 15, '0005', '20020901', 'n/a')
INSERT INTO item_list
VALUES (102, 'item_C', 180, '0005', '20020905', 'n/a')
SELECT acct,
name,
SUM(qty) AS total,
link,
MIN(d) AS first_date,
(SELECT memo
FROM item_list
WHERE acct = i.acct AND name = i.name AND link = i.link AND d = MIN(i.d)) AS
first_memo
FROM item_list AS i
GROUP BY acct, name, link
ORDER BY acct, name, link
which returns
acct name total link first_date first_memo
101 item_A 350 0001 2002-09-02 00:00:00.000 blah
101 item_A 80 0002 2002-09-03 00:00:00.000 n/a
101 item_B 90 0002 2002-08-08 00:00:00.000 n/a
101 item_B 220 0003 2002-09-02 00:00:00.000 abcd
102 item_B 200 0004 2002-09-03 00:00:00.000 xyz
102 item_C 195 0005 2002-09-01 00:00:00.000 n/a
Regards,
jag
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2002-09-24 00:11:26 | Re: [GENERAL] CURRENT_TIMESTAMP |
Previous Message | RVL | 2002-09-23 22:23:14 | HELP w/ SQL -- distinct select with non distinct fields? |