Home » SQL & PL/SQL » SQL & PL/SQL » i'm lazy - here's my pathetic title
| i'm lazy - here's my pathetic title [message #240161] |
Thu, 24 May 2007 04:56  |
kinkin20
Messages: 6
Registered: May 2007
|
Junior Member |
|
|
I also have 2 table
Table Customer
customer_key
gender (some row 'null' is ungender)
and Table Purchase
customer_key
purchase_price
Query: how much did male ,female and ungendered customer spend separately, and then for all customer?
I already got the values of the male and female and ungender
but i don't know how to get the value of al customer.
this is my query:
SELECT c.gender
,SUM(p.purchase_price)
FROM purchases p,customer c
GROUP BY c.gender
Please help me how to do it
thank a lot
[Updated on: Fri, 25 May 2007 07:50] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: I don' know how to do.....please help me [message #240168 is a reply to message #240161] |
Thu, 24 May 2007 05:10  |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
Have a look at GROUP BY ROLLUP.
I've taken the test data from your previous question. I'll sum the purchases per state and add the overal sum to it.
My test script:
CREATE TABLE customers( cust_id number
, state varchar2(2)
)
/
CREATE TABLE purchases( cust_id number
, price number check ( price > 0 )
)
/
INSERT INTO customers VALUES(1,'AA');
INSERT INTO customers VALUES(2,'AA');
INSERT INTO customers VALUES(3,'BB');
INSERT INTO customers VALUES(4,'BB');
INSERT INTO customers VALUES(5,'CC');
INSERT INTO customers VALUES(6,'CC');
PROMPT state 'AA' total = 700
INSERT INTO purchases VALUES( 1, 100);
INSERT INTO purchases VALUES( 1, 100);
INSERT INTO purchases VALUES( 1, 100);
INSERT INTO purchases VALUES( 2, 100);
INSERT INTO purchases VALUES( 2, 100);
INSERT INTO purchases VALUES( 2, 100);
INSERT INTO purchases VALUES( 2, 100);
PROMPT state 'BB' total = 900
INSERT INTO purchases VALUES( 3, 100);
INSERT INTO purchases VALUES( 3, 100);
INSERT INTO purchases VALUES( 3, 100);
INSERT INTO purchases VALUES( 3, 100);
INSERT INTO purchases VALUES( 3, 100);
INSERT INTO purchases VALUES( 4, 100);
INSERT INTO purchases VALUES( 4, 100);
INSERT INTO purchases VALUES( 4, 100);
INSERT INTO purchases VALUES( 4, 100);
PROMPT State 'CC' total = 200
INSERT INTO purchases VALUES( 5, 100);
INSERT INTO purchases VALUES( 5, 100);
SELECT DECODE(GROUPING(c.state),1, 'All',c.state) state
, SUM(p.price) tot
FROM customers c
, purchases p
WHERE p.cust_id = c.cust_id
GROUP BY ROLLUP (c.state)
/
DROP TABLE purchases
/
DROP TABLE customers
/
My test run:
SQL> @orafaq
Table created.
Table created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
state 'AA' total = 700
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
state 'BB' total = 900
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
State 'CC' total = 200
1 row created.
1 row created.
STA TOT
--- ----------
AA 700
BB 900
CC 200
All 1800
Table dropped.
Table dropped.
SQL>
Read the first topic (TIPS & TRICKS) to get a link to the Oracle documentation.
MHE
|
|
|
|
| Re: I don' know how to do.....please help me [message #240208 is a reply to message #240168] |
Thu, 24 May 2007 06:29 |
kinkin20
Messages: 6
Registered: May 2007
|
Junior Member |
|
|
thank Maaher
I already get the value of male, female and all customer by your query.
but it don't show customers having 'null' in row GENDER .I want to get both of them...male ,female ,ungender and all.
Please help me how to get ungenther more
thank
|
|
|
|
| Re: I don' know how to do.....please help me [message #240230 is a reply to message #240208] |
Thu, 24 May 2007 07:30 |
|
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
Ok, I've modified my script a little bit so that it is more appropriate for this question and ran it in SQL*Plus. Now I have:
CREATE TABLE customers( cust_id number
, gender varchar2(1)
)
/
CREATE TABLE purchases( cust_id number
, price number check ( price > 0 )
)
/
INSERT INTO customers VALUES(1,'M');
INSERT INTO customers VALUES(2,'M');
INSERT INTO customers VALUES(3,'M');
INSERT INTO customers VALUES(4,'F');
INSERT INTO customers VALUES(5,'F');
INSERT INTO customers VALUES(6,'F');
INSERT INTO customers VALUES(7, NULL);
INSERT INTO customers VALUES(8, NULL);
PROMPT gender 'M' total = 1200
INSERT INTO purchases VALUES( 1, 100);
INSERT INTO purchases VALUES( 1, 100);
INSERT INTO purchases VALUES( 1, 100);
INSERT INTO purchases VALUES( 2, 100);
INSERT INTO purchases VALUES( 2, 100);
INSERT INTO purchases VALUES( 2, 100);
INSERT INTO purchases VALUES( 2, 100);
INSERT INTO purchases VALUES( 3, 100);
INSERT INTO purchases VALUES( 3, 100);
INSERT INTO purchases VALUES( 3, 100);
INSERT INTO purchases VALUES( 3, 100);
INSERT INTO purchases VALUES( 3, 100);
PROMPT gender 'F' total = 600
INSERT INTO purchases VALUES( 4, 100);
INSERT INTO purchases VALUES( 4, 100);
INSERT INTO purchases VALUES( 4, 100);
INSERT INTO purchases VALUES( 4, 100);
INSERT INTO purchases VALUES( 5, 100);
INSERT INTO purchases VALUES( 5, 100);
PROMPT No gender total = 500
INSERT INTO purchases VALUES( 7, 100);
INSERT INTO purchases VALUES( 7, 100);
INSERT INTO purchases VALUES( 8, 100);
INSERT INTO purchases VALUES( 8, 100);
INSERT INTO purchases VALUES( 8, 100);
SELECT DECODE( GROUPING(c.gender)
, 1, 'All'
, Nvl(c.gender,'Ungender')
) gender
, SUM(p.price) tot
FROM customers c
, purchases p
WHERE p.cust_id = c.cust_id
GROUP BY ROLLUP (c.gender)
/
DROP TABLE purchases
/
DROP TABLE customers
/
The group by WILL take the NULL values into account, the NVL will give it a proper name. The GROUPING/ROLLUP will add an extra row for all the customers.
So, when I ran the script, I got this from the select:
GENDER TOT
-------- ----------
F 600
M 1200
Ungender 500
All 2300
Is this what you're after?
MHE
[Updated on: Thu, 24 May 2007 07:30] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Sat Dec 14 00:25:40 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
