SQL: GROUP BY-statement without using group-by-CodePudding

Question: HOW can I get the same result for the below query but without using GROUP BY?

Can someone provide me with a solution that does the same, but without using GROUP BY:

SELECT Klasse, COUNT() AS Anzahl FROM Charaktere WHERE Schaden > 700 GROUP BY Klasse HAVING COUNT() > 1

The statement MUST function on an oracle-server.

Test-data:

CREATE TABLE Charaktere (
    Charakter_ID varchar(300),
    Name varchar(300),
    Klasse varchar(300),
    Rasse varchar(300),
    Stufe varchar(300),
    Leben_Multiplikator varchar(300),
    Mana_Multiplikator varchar(300),
    Rüstung varchar(300),
    Waffen_ID varchar(300),
    Schaden varchar(300)
);

CREATE TABLE Klassen (
    Klassen_ID varchar(300),
    Klasse varchar(300),
    Basisleben varchar(300),
    Basismana varchar(300),
    Schwächen varchar(300)
);

CREATE TABLE Ausrüstung (
    Ausrüstung_ID varchar(300),
    Rüstung varchar(300),
    Schmuck varchar(300)
);

CREATE TABLE Waffen (
    Waffen_ID varchar(300),
    Links varchar(300),
    Rechts varchar(300)
);

INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('1','Herald','Zauberer','Mensch','67','2','8','Heilig','4','718');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('2','Roderic','Paladin','Mensch','55','10','3','Schwer','2','691');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('3','Favian','Schurke','Ork','32','4','1','Leicht','3','243');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('4','Vega','Berserker','Zwerg','44','9','8','Schwer','2','118');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('5','Matep','Jäger','Dunkel Elf','24','3','6','Leicht','1','368');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('6','Euris','Kleriker','Mensch','77','7','8','Resistent','4','774');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('7','Dara’a','Nekromant','Blut Elf','99','6','1','Verdorben','5','966');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('8','Eodriel','Magier','Hoch Elf','24','2','3','Resistent','5','399');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('9','Kerodan','Magier','Blut Elf','20','6','2','Heilig','4','758');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('10','Hans','Paladin','Mensch','67','7','9','Schwer','2','632');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('11','Falk','Berserker','Mensch','13','8','6','Leicht','2','149');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('12','Sethrak','Paladin','Ork','54','5','1','Schwer','3','657');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('13','Hozen','Kleriker','Zwerg','68','6','3','Heilig','4','710');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('14','Venthyr','Jäger','Dunkel Elf','23','4','7','Leicht','1','197');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('15','Stanford','Paladin','Mensch','56','3','7','Resistent','2','370');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('16','Celoevalin','Zauberer','Blut Elf','8','3','6','Heilig','4','383');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('17','Sylvar','Berserker','Hoch Elf','76','9','4','Verdorben','2','837');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('18','Kyrian','Zauberer','Zwerg','69','6','3','Heilig','5','756');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('19','Ithris','Kleriker','Dunkel Elf','88','9','6','Resistent','4','500');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('20','Diedrich','Magier','Mensch','1','2','2','Heilig','2','102');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('21','Dar’mir','Jäger','Blut Elf','14','1','7','Leicht','1','150');

INSERT INTO Klassen (Klassen_ID,Klasse,Basisleben,Basismana,Schwächen) VALUES ('1','Zauberer','70','170','Paladin');
INSERT INTO Klassen (Klassen_ID,Klasse,Basisleben,Basismana,Schwächen) VALUES ('2','Paladin','150','110','Zauberer');
INSERT INTO Klassen (Klassen_ID,Klasse,Basisleben,Basismana,Schwächen) VALUES ('3','Schurke','100','100','Magier');
INSERT INTO Klassen (Klassen_ID,Klasse,Basisleben,Basismana,Schwächen) VALUES ('4','Berserker','200','80','Jäger');
INSERT INTO Klassen (Klassen_ID,Klasse,Basisleben,Basismana,Schwächen) VALUES ('5','Jäger','110','100','Schurke');
INSERT INTO Klassen (Klassen_ID,Klasse,Basisleben,Basismana,Schwächen) VALUES ('6','Kleriker','95','120','Nekromant');
INSERT INTO Klassen (Klassen_ID,Klasse,Basisleben,Basismana,Schwächen) VALUES ('7','Nekromant','50','200','Paladin');
INSERT INTO Klassen (Klassen_ID,Klasse,Basisleben,Basismana,Schwächen) VALUES ('8','Magier','85','150','Berserker');

INSERT INTO Ausrüstung (Ausrüstung_ID,Rüstung,Schmuck) VALUES ('1','Schwer','Kette');
INSERT INTO Ausrüstung (Ausrüstung_ID,Rüstung,Schmuck) VALUES ('2','Leicht','Armreif');
INSERT INTO Ausrüstung (Ausrüstung_ID,Rüstung,Schmuck) VALUES ('3','Resistent','Anhänger');
INSERT INTO Ausrüstung (Ausrüstung_ID,Rüstung,Schmuck) VALUES ('4','Heilig','Ring');
INSERT INTO Ausrüstung (Ausrüstung_ID,Rüstung,Schmuck) VALUES ('5','Verdorben','Talisman');

INSERT INTO Waffen (Waffen_ID,Links,Rechts) VALUES ('1','Bogen','Dolch');
INSERT INTO Waffen (Waffen_ID,Links,Rechts) VALUES ('2','Langschwert',NULL);
INSERT INTO Waffen (Waffen_ID,Links,Rechts) VALUES ('3','Axt','Axt');
INSERT INTO Waffen (Waffen_ID,Links,Rechts) VALUES ('4','Zauberstab','Zauberbuch');
INSERT INTO Waffen (Waffen_ID,Links,Rechts) VALUES ('5','Zauberbuch','Zauberbuch');

Please don't lecture me about the why's and so on ... I just need an answer to my question :-)

CodePudding user response：

Ok, one way would be using COUNT with the OVER clause, and DISTINCT to get the different results:

SELECT DISTINCT Klasse, Anzahl
FROM (
       SELECT Klasse, COUNT(*) OVER(PARTITION BY Klasse) AS Anzahl
       FROM Charaktere
       WHERE Schaden > 700
     ) 
WHERE Anzahl > 1;

The results are:

Klasse	Anzahl
Kleriker	2
Zauberer	2

CodePudding user response：

Assuming there is a primary key constraint on Klassen.Klasse and a foreign key constraint from Charaktere.Klasse to it (or an intended one if the constraint does not actually exist since you seem to have no constraints on your sample data), then you can use:

SELECT *
FROM   (
  SELECT Klasse,
         ( SELECT COUNT(*)
           FROM   Charaktere c
           WHERE  Schaden > 700
           AND    c.Klasse = K.Klasse ) AS anzhal
  FROM   Klassen k
)
WHERE  anzhal > 1;

or:

SELECT k.klasse,
       c.anzhal
FROM   Klassen k
       CROSS JOIN LATERAL (
         SELECT COUNT(*) AS anzhal
         FROM   Charaktere c
         WHERE  Schaden > 700
         AND    c.Klasse = K.Klasse
       ) c
WHERE  c.anzhal > 1;

Which, for your sample data, both output:

KLASSE	ANZHAL
Zauberer	2
Kleriker	2

fiddle

CodePudding user response：

If you don't want (for whatever reason) use a clear and readable group by, then you may:

use pivot that groups implicitly

with tab as (
  select
    Klasse,
    'A' as col
  from Charaktere 
  where schaden > 700
)
select *
from tab
pivot (
  count(1)
  for col in ('A' as anzahl)
)
where anzahl > 1

or perform calculations by match_recognize's aggregation capability

select
  Klasse,
  cnt AS Anzahl 
from Charaktere 
match_recognize (
  partition by klasse
  order by schaden
  measures
    count(1) as cnt
  pattern (a a )
  /*More than one consecutive klasse
  with schaden > 700*/
  define
    a as schaden > 700
)
order by 1

or use lateral join


select distinct
  c.klasse,
  l.anzahl
from Charaktere c
  cross join lateral (
    select
      count(1) as anzahl
    from Charaktere c2
    where c2.schaden > 700
    and c2.klasse = c.klasse
  ) l
where l.anzahl > 1
order by 1

or even with help of XQuery and serialization to XMLType

select *
from xmltable(
'(: Grouping by distinct :)
for $x in distinct-values($rs/ROWSET/ROW/KLASSE)
let $cnt := count($rs/ROWSET/ROW[KLASSE=$x])
where $cnt > 1
return <row klasse="{$x}" anzahl="{$cnt}"/>'
  passing dbms_xmlgen.getxmltype(
'select
  Klasse
from Charaktere 
where schaden > 700') as "rs"

columns
  klasse varchar2(100) path '@klasse',
  anzahl int path '@anzahl'
)
order by 1

All the above return this result for your sample data:

KLASSE	ANZAHL
Kleriker	2
Zauberer	2

fiddle