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 Go to next message
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 #240164 is a reply to message #240161] Thu, 24 May 2007 05:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
break on report
compute sum of sumprice on report
SELECT c.gender, SUM(p.purchase_price) sumprice
FROM purchases p,customer c
GROUP BY c.gender
/

Regards
Michel
Re: I don' know how to do.....please help me [message #240168 is a reply to message #240161] Thu, 24 May 2007 05:10 Go to previous messageGo to next message
Maaher
Messages: 7062
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Maaher
Messages: 7062
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

Re: I don' know how to do.....please help me [message #240536 is a reply to message #240230] Fri, 25 May 2007 03:17 Go to previous message
kinkin20
Messages: 6
Registered: May 2007
Junior Member
Thank
i finished my homework.
thank a lot for your help.
Previous Topic: Materialized view ON COMMIT 12054 error - but it almost works!
Next Topic: script help needed
Goto Forum:
  


Current Time: Fri Dec 09 11:49:41 CST 2016

Total time taken to generate the page: 0.27912 seconds