Home » SQL & PL/SQL » SQL & PL/SQL » COUNT DISTINCT DECODE
COUNT DISTINCT DECODE [message #11389] Wed, 24 March 2004 07:00 Go to next message
Tim
Messages: 49
Registered: October 2000
Member
I want to get a column that counts lakes and a column that counts streams.  The following works but counts multiple instances of the same lake or stream:

<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">

SELECT SPECIES_CODE,SIZE_CODE, SUM(LBS_STOCKED),SUM(NO_STOCKED),

COUNT (DECODE( water_body,'lake',1)) as LAKES,

COUNT(DECODE( water_body,'stream',1)) as streams

FROM TRANSACTIONS

WHERE TO_CHAR(TRANSACTION_DATE,'YYYY') = 2003

GROUP BY SPECIES_CODE,SIZE_CODE
</BLOCKQUOTE>

So I tried:

<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">

SELECT SPECIES_CODE,SIZE_CODE, SUM(LBS_STOCKED),SUM(NO_STOCKED),

COUNT (DISTINCT DECODE( water_body,'disposition',1)) as LAKES,

COUNT(DISTINCT DECODE( water_body,'stream',1)) as streams

FROM TRANSACTIONS

WHERE TO_CHAR(TRANSACTION_DATE,'YYYY') = 2003

GROUP BY SPECIES_CODE,SIZE_CODE
</BLOCKQUOTE>

but that gets me a 1 if there are any lakes at all and a 0 if there are no lakes. 

What am I missing?

Thank you all
Re: COUNT DISTINCT DECODE [message #11390 is a reply to message #11389] Wed, 24 March 2004 07:20 Go to previous messageGo to next message
Art Metzer
Messages: 2477
Registered: December 2002
Senior Member
A request for clarification:

It looks like your water_body column tells you whether you are dealing with a lake or a stream, but which column distinguishes one lake/stream from another lake/stream?

I.e., if I have two rows with a water_body of 'stream', how do I know whether those rows refer to the same stream or different streams?

A.
Re: COUNT DISTINCT DECODE [message #11391 is a reply to message #11390] Wed, 24 March 2004 07:49 Go to previous messageGo to next message
Tim
Messages: 49
Registered: October 2000
Member
Aha. Your question made me realize I am just getting distinct values for the water_body column and not the WATER_DOW column.

WATER_DOW is the identifier for a specific body of water.
How would I mix that into my statement?

thank you for that clarification.
Re: COUNT DISTINCT DECODE [message #11394 is a reply to message #11391] Wed, 24 March 2004 08:14 Go to previous messageGo to next message
Art Metzer
Messages: 2477
Registered: December 2002
Senior Member
Here's the trick I used to get your DISTINCT lakes and streams. Please note, this trick uses analytic functions, and therefore requires at least Oracle version 8.1.6.

The in-line view tx just grabs all the necessary columns from transactions, and in addition, adds the rn column, which uses the ROW_NUMBER() analytic function. All this column does is assign a unique number to multiple combinations of species_code, size_code, water_body and water_dow.

Then the outer SELECT only sums up rows where this rn is 1, in effect getting a DISTINCT count of body of water per species_code and size_code.
SQL> CREATE TABLE transactions (
  2      species_code        NUMBER
  3  ,   size_code           VARCHAR2(9)
  4  ,   lbs_stocked         NUMBER
  5  ,   no_stocked          NUMBER
  6  ,   water_body          VARCHAR2(6)
  7  ,   water_dow           NUMBER
  8  ,   transaction_date    DATE
  9  );
 
Table created.
 
SQL> INSERT INTO transactions VALUES (1001,'A',200,60,'lake',188,TO_DATE('20030303','YYYYMMDD'));
SQL> INSERT INTO transactions VALUES (1001,'B',500,123,'lake',188,TO_DATE('20030728','YYYYMMDD'));
SQL> INSERT INTO transactions VALUES (1001,'B',50,9,'stream',292,TO_DATE('20030225','YYYYMMDD'));
SQL> INSERT INTO transactions VALUES (1002,'A',202,44,'lake',188,TO_DATE('20030615','YYYYMMDD'));
SQL> INSERT INTO transactions VALUES (1002,'A',75,19,'lake',189,TO_DATE('20030729','YYYYMMDD'));
SQL> INSERT INTO transactions VALUES (1002,'A',34,14,'lake',190,TO_DATE('20030803','YYYYMMDD'));
SQL> INSERT INTO transactions VALUES (1002,'A',20,8,'lake',190,TO_DATE('20030804','YYYYMMDD'));
SQL> INSERT INTO transactions VALUES (1002,'A',155,53,'stream',291,TO_DATE('20030805','YYYYMMDD'));
SQL> INSERT INTO transactions VALUES (1002,'A',39,14,'stream',292,TO_DATE('20030806','YYYYMMDD'));
SQL> INSERT INTO transactions VALUES (1002,'A',67,22,'stream',293,TO_DATE('20030807','YYYYMMDD'));
SQL> INSERT INTO transactions VALUES (1002,'A',66,20,'stream',294,TO_DATE('20030808','YYYYMMDD'));
SQL> INSERT INTO transactions VALUES (1002,'B',46,14,'lake',186,TO_DATE('20030411','YYYYMMDD'));
SQL> INSERT INTO transactions VALUES (1002,'B',77,22,'lake',187,TO_DATE('20030509','YYYYMMDD'));
SQL> INSERT INTO transactions VALUES (1002,'B',92,37,'lake',186,TO_DATE('20030510','YYYYMMDD'));
SQL> INSERT INTO transactions VALUES (1002,'B',25,9,'lake',187,TO_DATE('20030601','YYYYMMDD'));
SQL> INSERT INTO transactions VALUES (1002,'C',59,12,'lake',180,TO_DATE('20030525','YYYYMMDD'));
SQL> INSERT INTO transactions VALUES (1002,'C',120,24,'lake',181,TO_DATE('20030526','YYYYMMDD'));
SQL> INSERT INTO transactions VALUES (1002,'C',67,11,'lake',182,TO_DATE('20030527','YYYYMMDD'));
SQL> INSERT INTO transactions VALUES (1002,'C',49,10,'lake',183,TO_DATE('20030528','YYYYMMDD'));
SQL> INSERT INTO transactions VALUES (1002,'C',199,37,'lake',179,TO_DATE('20030601','YYYYMMDD'));
SQL> INSERT INTO transactions VALUES (1002,'C',202,36,'stream',275,TO_DATE('20030613','YYYYMMDD'));
SQL> INSERT INTO transactions VALUES (1002,'C',19,4,'stream',275,TO_DATE('20030614','YYYYMMDD'));
SQL> INSERT INTO transactions VALUES (1002,'C',417,79,'stream',276,TO_DATE('20030615','YYYYMMDD'));
SQL> COMMIT;
 
Commit complete.
 
SQL> SELECT   tx.species_code
  2  ,        tx.size_code
  3  ,        SUM(tx.lbs_stocked)  total_lbs_stocked
  4  ,        SUM(tx.no_stocked)   total_no_stocked
  5  ,        SUM(DECODE(tx.rn
  6               ,      1, DECODE(tx.water_body
  7                         ,      'lake', 1
  8                         ,      0)
  9               ,      0))       nbr_lakes
 10  ,        SUM(DECODE(tx.rn
 11               ,      1, DECODE(tx.water_body
 12                         ,      'stream', 1
 13                         ,      0)
 14               ,      0))       nbr_streams
 15  FROM    (SELECT t.species_code
 16           ,      t.size_code
 17           ,      t.lbs_stocked
 18           ,      t.no_stocked
 19           ,      t.water_body
 20           ,      t.transaction_date
 21           ,      ROW_NUMBER()
 22                  OVER (PARTITION BY t.species_code
 23                        ,            t.size_code
 24                        ,            t.water_body
 25                        ,            t.water_dow
 26                        ORDER BY     NULL)        rn
 27           FROM   transactions       t)           tx
 28  WHERE    TO_NUMBER(TO_CHAR(tx.transaction_date,'YYYY')) = 2003
 29  GROUP BY tx.species_code
 30  ,        tx.size_code
 31  ORDER BY tx.species_code
 32  ,        tx.size_code
 33  /
 
SPECIES_CODE SIZE_CODE TOTAL_LBS_STOCKED TOTAL_NO_STOCKED  NBR_LAKES NBR_STREAMS
------------ --------- ----------------- ---------------- ---------- -----------
        1001 A                       200               60          1           0
        1001 B                       550              132          1           1
        1002 A                       658              194          3           4
        1002 B                       240               82          2           0
        1002 C                      1132              213          5           2
 
SQL>
HTH,

Art.
Re: COUNT DISTINCT DECODE [message #11395 is a reply to message #11394] Wed, 24 March 2004 10:55 Go to previous message
Tim
Messages: 49
Registered: October 2000
Member
Wow! I will have to look into the PARTITION command some more.

What I ended up doing was creating a view that had all the lakes and streams totalled and then ran my DECODE commmand.

Thank you for your attention, Art.

Tim
Previous Topic: Oracle Processes
Next Topic: SQLPLUS: Newbee
Goto Forum:
  


Current Time: Fri Nov 28 17:35:14 CST 2014

Total time taken to generate the page: 0.15207 seconds