Home » SQL & PL/SQL » SQL & PL/SQL » Another Pivot question (Oracle 10.2)
Another Pivot question [message #576520] Tue, 05 February 2013 15:47 Go to next message
lott42
Messages: 96
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

Re: Another Pivot question [message #576521 is a reply to message #576520] Tue, 05 February 2013 15:52 Go to previous messageGo to next message
Littlefoot
Messages: 19600
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;
Re: Another Pivot question [message #576523 is a reply to message #576521] Tue, 05 February 2013 16:09 Go to previous messageGo to next message
lott42
Messages: 96
Registered: June 2010
Member
Damn, I mean Thank You!! That was it Razz
Re: Another Pivot question [message #576537 is a reply to message #576523] Wed, 06 February 2013 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 59072
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

Re: Another Pivot question [message #576612 is a reply to message #576537] Wed, 06 February 2013 09:57 Go to previous messageGo to next message
lott42
Messages: 96
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 Go to previous messageGo to next message
Michel Cadot
Messages: 59072
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your query gives the correct result, no?

Regards
Michel
Re: Another Pivot question [message #576615 is a reply to message #576614] Wed, 06 February 2013 10:30 Go to previous messageGo to next message
Michel Cadot
Messages: 59072
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
Re: Another Pivot question [message #576617 is a reply to message #576615] Wed, 06 February 2013 10:33 Go to previous message
lott42
Messages: 96
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)
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:
  


Current Time: Tue Sep 16 00:00:10 CDT 2014

Total time taken to generate the page: 0.18390 seconds