pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTable Join (Maybe?)
4+ messages / 3 participants
[nested] [flat]
* Table Join (Maybe?)
@ 2006-07-19 07:34 Phillip Smith <phillips@weatherbeeta.com.au>
2006-07-19 11:13 ` Re: Table Join (Maybe?) Richard Broersma Jr <rabroersma@yahoo.com>
2006-07-19 14:29 ` Re: Table Join (Maybe?) Erik Jones <erik@myemma.com>
0 siblings, 2 replies; 4+ messages in thread
From: Phillip Smith @ 2006-07-19 07:34 UTC (permalink / raw)
To: pgsql-sql
Hi again all,
I have two tables:
1. Sales figures by date and customer.
2. Customer details - including their Geographic State
I need to extract a report from the first table (I can do that!), and in
that report order by their State (I can do that too!), but I also need a
summary of all the customers in each state, below the end of each state, and
have a grand total at the bottom.
Eg:
Customer 1 State 1 $100.00
Customer 2 State 1 $100.00
State 1 $200.00
Customer 3 State 2 $100.00
Customer 4 State 2 $100.00
State 2 $200.00
Grand Total $400.00
Does anyone have any magic pointers for me? I've been playing with SELECT
INTO as 2 queries (the individual customers, then the summary figures added
to the temp table) but I end up with ROWS IN FIRST QUERY * ROWS IN SECOND
QUERY instead of them all sorted together nicely :-(
Thanks all,
-p
Table Defs:
CREATE TABLE sales_figures
(
rep_date date NOT NULL,
store varchar(6) NOT NULL,
sales_value numeric DEFAULT 0,
sales_customers int4 DEFAULT 0,
CONSTRAINT sales_figures_pkey PRIMARY KEY (rep_date, store),
CONSTRAINT sales_figures_store FOREIGN KEY (store)
REFERENCES stores (code) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
CREATE TABLE stores
(
code varchar(2) NOT NULL DEFAULT ''::character varying,
name varchar(32) NOT NULL DEFAULT ''::character varying,
bms varchar(1) DEFAULT 'Y'::character varying,
state text DEFAULT 'UNKNOWN'::text,
business_open varchar(1) DEFAULT 'Y'::character varying,
CONSTRAINT stores_pkey PRIMARY KEY (code)
)
Phillip Smith
IT Coordinator
Weatherbeeta P/L
8 Moncrief Rd
Nunawading, VIC, 3131
AUSTRALIA
*******************Confidentiality and Privilege Notice*******************
The material contained in this message is privileged and confidential to
the addressee. If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.
Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Table Join (Maybe?)
2006-07-19 07:34 Table Join (Maybe?) Phillip Smith <phillips@weatherbeeta.com.au>
@ 2006-07-19 11:13 ` Richard Broersma Jr <rabroersma@yahoo.com>
1 sibling, 0 replies; 4+ messages in thread
From: Richard Broersma Jr @ 2006-07-19 11:13 UTC (permalink / raw)
To: Phillip Smith <phillips@weatherbeeta.com.au>; pgsql-sql
> I have two tables:
> 1. Sales figures by date and customer.
> 2. Customer details - including their Geographic State
> I need to extract a report from the first table (I can do that!), and in
> that report order by their State (I can do that too!), but I also need a
> summary of all the customers in each state, below the end of each state, and
> have a grand total at the bottom.
> Eg:
> Customer 1 State 1 $100.00
> Customer 2 State 1 $100.00
> State 1 $200.00
> Customer 3 State 2 $100.00
> Customer 4 State 2 $100.00
> State 2 $200.00
> Grand Total $400.00
> Does anyone have any magic pointers for me? I've been playing with SELECT
> INTO as 2 queries (the individual customers, then the summary figures added
> to the temp table) but I end up with ROWS IN FIRST QUERY * ROWS IN SECOND
> QUERY instead of them all sorted together nicely :-(
I do not believe that a single "query" will give you what you want here. YOU could have 3
seperate queries:
1 for customer detail by state.
1 for state total
1 for grand total
However, if you use a reporting tool like crystal or others, they have the ability to generate
summaries exactly as you are referring to here. you would only need to pass it the query on total
per customer. The reporting utility has a groupby feature where it would group the customers by
state for your. In the group by summary it would automaticly display state total. Then if the
report footer you could add a grand total summary for all of the records you passed to the report.
The following link show some of the reporting programs that you can use.
http://www.postgresql.org/community/survey.43
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Table Join (Maybe?)
2006-07-19 07:34 Table Join (Maybe?) Phillip Smith <phillips@weatherbeeta.com.au>
@ 2006-07-19 14:29 ` Erik Jones <erik@myemma.com>
1 sibling, 0 replies; 4+ messages in thread
From: Erik Jones @ 2006-07-19 14:29 UTC (permalink / raw)
To: Phillip Smith <phillips@weatherbeeta.com.au>; +Cc: pgsql-sql
Phillip Smith wrote:
>
> Hi again all,
>
> I have two tables:
>
> 1. Sales figures by date and customer.
>
> 2. Customer details – including their Geographic State
>
> I need to extract a report from the first table (I can do that!), and
> in that report order by their State (I can do that too!), but I also
> need a summary of all the customers in each state, below the end of
> each state, and have a grand total at the bottom.
>
> Eg:
>
> Customer 1 State 1 $100.00
>
> Customer 2 State 1 $100.00
>
> State 1 $200.00
>
> Customer 3 State 2 $100.00
>
> Customer 4 State 2 $100.00
>
> State 2 $200.00
>
> Grand Total $400.00
>
> Does anyone have any magic pointers for me? I’ve been playing with
> SELECT INTO as 2 queries (the individual customers, then the summary
> figures added to the temp table) but I end up with ROWS IN FIRST QUERY
> * ROWS IN SECOND QUERY instead of them all sorted together nicely L
>
> Thanks all,
>
> -p
>
Well, two queries one for the individual totals and one for the summary
totals is good (maybe a third for the grand total), but you should do a
union of the two and then play with the order by and/or group by clauses
(depending on the data) to get the ordering that you want. I can't even
count the times I've spent banging my head against the proverbial wall
(you do have a proverbial wall don't you?) trying to get these kinds of
queries to work with joins, sub-queries, case statements, etc... only to
come back to using union on simple, to-the-point queries.
--
erik jones <erik@myemma.com>
software development
emma(r)
^ permalink raw reply [nested|flat] 4+ messages in thread
* FW: Table Join (Maybe?)
@ 2006-07-19 23:51 Phillip Smith <phillips@weatherbeeta.com.au>
0 siblings, 0 replies; 4+ messages in thread
From: Phillip Smith @ 2006-07-19 23:51 UTC (permalink / raw)
To: pgsql-sql
Thanks all for your suggestions - the below suggestion works perfectly.
A little tweak of the column counts and group by clauses makes it work like
a charm.
Richard - this particular extract is via PHP to a web page so I can't use
those reporting tools, but I'll have a look and fiddle for my next project -
Thanks!
Cheers,
-p
-----Original Message-----
From: Arulmani V A [mailto:arulmani_s@yahoo.com]
Sent: Wednesday, 19 July 2006 20:49
To: Phillip Smith
Subject: Re: [SQL] Table Join (Maybe?)
Hi,
Postgres does not seem to support OLAP functions like ROLLUP, CUBE, etc. I'm
not a Postgres expert, but I think we can try the following alternative
approach :
Sample data for the two tables given by you are created as :
Table : stores
code name bms state business_open
1 Customer1 Y State1 Y
2 Customer2 Y State2 Y
3 Customer3 Y State1 Y
4 Customer4 Y State2 Y
Table : sales_figures
rep_date store sales_value sales_customers
2006-01-01 1 1000 0
2006-01-02 1 1000 0
2006-02-01 2 200 0
2006-02-02 2 200 0
2006-02-03 2 500 0
2006-01-03 1 100 0
2006-01-01 3 300 0
2006-01-02 3 200 0
2006-02-01 4 700 0
2006-02-02 4 400 0
If I execute the following query :
SELECT a.name, a.state, SUM(b.sales_value) FROM stores a, sales_figures b
WHERE a.code = b.store GROUP BY a.state, a.name
UNION
SELECT NULL, a.state, SUM(b.sales_value) FROM stores a, sales_figures b
WHERE a.code = b.store GROUP BY a.state
UNION
SELECT 'GRAND TOTAL', NULL, SUM(b.sales_value) FROM stores a, sales_figures
b WHERE a.code =
b.store ORDER BY 2
I get the following output (same as what you expect?) :
name state sum
Customer1 State1 2100
Customer3 State1 500
NULL State1 2600
Customer2 State2 900
Customer4 State2 1100
NULL State2 2000
GRAND TOTAL NULL 4600
Is the above approach OK?
Regards
Arul
----- Original Message ----
From: Phillip Smith <phillips@weatherbeeta.com.au>
To: pgsql-sql@postgresql.org
Sent: Wednesday, July 19, 2006 1:04:58 PM
Subject: [SQL] Table Join (Maybe?)
Hi again all,
I have two tables:
1. Sales figures by date and customer.
2. Customer details - including their Geographic State
I need to extract a report from the first table (I can do that!), and in
that report order by their State (I can do that too!), but I also need a
summary of all the customers in each state, below the end of each state, and
have a grand total at the bottom.
Eg:
Customer 1 State 1 $100.00
Customer 2 State 1 $100.00
State 1 $200.00
Customer 3 State 2 $100.00
Customer 4 State 2 $100.00
State 2 $200.00
Grand Total $400.00
Does anyone have any magic pointers for me? I've been playing with SELECT
INTO as 2 queries (the individual customers, then the summary figures added
to the temp table) but I end up with ROWS IN FIRST QUERY * ROWS IN SECOND
QUERY instead of them all sorted together nicely :-(
Thanks all,
-p
Table Defs:
CREATE TABLE sales_figures
(
rep_date date NOT NULL,
store varchar(6) NOT NULL,
sales_value numeric DEFAULT 0,
sales_customers int4 DEFAULT 0,
CONSTRAINT sales_figures_pkey PRIMARY KEY (rep_date, store),
CONSTRAINT sales_figures_store FOREIGN KEY (store)
REFERENCES stores (code) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
CREATE TABLE stores
(
code varchar(2) NOT NULL DEFAULT ''::character varying,
name varchar(32) NOT NULL DEFAULT ''::character varying,
bms varchar(1) DEFAULT 'Y'::character varying,
state text DEFAULT 'UNKNOWN'::text,
business_open varchar(1) DEFAULT 'Y'::character varying,
CONSTRAINT stores_pkey PRIMARY KEY (code)
)
Phillip Smith
IT Coordinator
Weatherbeeta P/L
8 Moncrief Rd
Nunawading, VIC, 3131
AUSTRALIA
*******************Confidentiality and Privilege Notice*******************
The material contained in this message is privileged and confidential to the
addressee. If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy or
deliver this message to anyone, and you should destroy it and kindly notify
the sender by reply email.
Information in this message that does not relate to the official business of
Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
*******************Confidentiality and Privilege Notice*******************
The material contained in this message is privileged and confidential to
the addressee. If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.
Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-07-19 23:51 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-19 07:34 Table Join (Maybe?) Phillip Smith <phillips@weatherbeeta.com.au>
2006-07-19 11:13 ` Richard Broersma Jr <rabroersma@yahoo.com>
2006-07-19 14:29 ` Erik Jones <erik@myemma.com>
2006-07-19 23:51 FW: Table Join (Maybe?) Phillip Smith <phillips@weatherbeeta.com.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox