OraFAQ Forum: SQL & PL/SQL » Another Pivot question

Home » SQL & PL/SQL » SQL & PL/SQL » Another Pivot question (Oracle 10.2)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Another Pivot question [message #576520]

Tue, 05 February 2013 15:47

lott42
Messages: 54
Registered: June 2010

Member

I'm trying to get a "pivot" count of data. I think I'm close but I get
error: "ORA 937" Not a single group function"

I think I'm close but something is missing...

with data as (
select 1 pk, 1 val, 'A' type from dual union all
select 1 pk, 1 val, 'B' type from dual union all
select 1 pk, 2 val, 'A' type from dual union all
select 2 pk, 1 val, 'A' type from dual union all
select 2 pk, 2 val, 'C' type from dual
)
SELECT val,
       MAX(decode(TYPE, 'A', COUNT(*))) A_Type,
       MAX(decode(TYPE, 'B', COUNT(*))) B_Type,
       MAX(decode(TYPE, 'C', COUNT(*))) C_Type
  FROM data
 WHERE pk = 1
 GROUP BY val

I'd like the result similar to:

Result
Val    A_Type           B_Type          C_Type
1      1                1               0
2      1                0               0

Report message to a moderator

Re: Another Pivot question [message #576521 is a reply to message #576520]

Tue, 05 February 2013 15:52

Littlefoot
Messages: 16997
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

You were close!

with ...
SELECT val,
       sum(decode(TYPE, 'A', 1, 0)) A_Type,
       sum(decode(TYPE, 'B', 1, 0)) B_Type,
       sum(decode(TYPE, 'C', 1, 0)) C_Type
  FROM data
 WHERE pk = 1
 GROUP BY val;

Report message to a moderator

Re: Another Pivot question [message #576523 is a reply to message #576521]

Tue, 05 February 2013 16:09

lott42
Messages: 54
Registered: June 2010

Member

Damn, I mean Thank You!! That was it Razz

Report message to a moderator

Re: Another Pivot question [message #576537 is a reply to message #576523]

Wed, 06 February 2013 00:57

Michel Cadot
Messages: 54246
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Or better, if you want to count, use count:

with ...
SELECT val,
       count(decode(TYPE, 'A', 1)) A_Type,
       count(decode(TYPE, 'B', 1)) B_Type,
       count(decode(TYPE, 'C', 1)) C_Type
  FROM data
 WHERE pk = 1
 GROUP BY val;

Regards
Michel

Report message to a moderator

Re: Another Pivot question [message #576612 is a reply to message #576537]

Wed, 06 February 2013 09:57

lott42
Messages: 54
Registered: June 2010

Member

Thanks Michel for that version too.

What if I wanted to just find out if any of the "buckets"("A", "B", "C") actually exists?? I realize I can derive it by the count or sum but how about a "Y/N"

WITH data AS
 (SELECT 1 pk, 1 val, 'A' TYPE
    FROM dual
  UNION ALL
  SELECT 1 pk, 1 val, 'B' TYPE
    FROM dual
  UNION ALL
  SELECT 1 pk, 2 val, 'A' TYPE
    FROM dual
  UNION ALL
  --New row
  SELECT 1 pk, 1 val, 'A' TYPE
    FROM dual
  UNION ALL
  SELECT 2 pk, 1 val, 'A' TYPE
    FROM dual
  UNION ALL
  SELECT 2 pk, 2 val, 'C' TYPE FROM dual)
SELECT val,
       decode(COUNT(decode(TYPE, 'A', 1)), 0, 'N', 'Y') A_Type,
       decode(COUNT(decode(TYPE, 'B', 1)), 0, 'N', 'Y') B_Type,
       decode(COUNT(decode(TYPE, 'C', 1)), 0, 'N', 'Y') C_Type
  FROM data
 WHERE pk = 1
 GROUP BY val;

[Updated on: Wed, 06 February 2013 10:03]

Report message to a moderator

Re: Another Pivot question [message #576614 is a reply to message #576612]

Wed, 06 February 2013 10:27

Michel Cadot
Messages: 54246
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Your query gives the correct result, no?

Regards
Michel

Report message to a moderator

Re: Another Pivot question [message #576615 is a reply to message #576614]

Wed, 06 February 2013 10:30

Michel Cadot
Messages: 54246
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Without counting:

SQL> WITH data AS
  2   (SELECT 1 pk, 1 val, 'A' TYPE
  3      FROM dual
  4    UNION ALL
  5    SELECT 1 pk, 1 val, 'B' TYPE
  6      FROM dual
  7    UNION ALL
  8    SELECT 1 pk, 2 val, 'A' TYPE
  9      FROM dual
 10    UNION ALL
 11    --New row
 12    SELECT 1 pk, 1 val, 'A' TYPE
 13      FROM dual
 14    UNION ALL
 15    SELECT 2 pk, 1 val, 'A' TYPE
 16      FROM dual
 17    UNION ALL
 18    SELECT 2 pk, 2 val, 'C' TYPE FROM dual)
 19  select val, 
 20         nvl(max(decode(TYPE, 'A', 'Y')),'N') A_type,
 21         nvl(max(decode(TYPE, 'B', 'Y')),'N') B_type,
 22         nvl(max(decode(TYPE, 'C', 'Y')),'N') C_type
 23   FROM data
 24   WHERE pk = 1
 25   GROUP BY val;
       VAL A B C
---------- - - -
         1 Y Y N
         2 Y N N

Regards
Michel

Report message to a moderator

Re: Another Pivot question [message #576617 is a reply to message #576615]

Wed, 06 February 2013 10:33

lott42
Messages: 54
Registered: June 2010

Member

@Michel, here was my reply to your prior response
yes it does but it seemed a little "convoluted". I thought there might be a "cleaner" way to write it.

but you already gave me another version!!

Thanks Surprised

)

Report message to a moderator

Previous Topic:	Please provide function for going at start and end time for the day from sysdate
Next Topic:	How to find out Date Format

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 25 08:42:31 CDT 2013

Total time taken to generate the page: 0.12017 seconds