iceman225 iceman225 - 3 months ago 16
SQL Question

materialized view using WITH statement

i created a materialized view but i have a mistake i do not understand to resolve it

RA-00937: not a single-group group function
00937. 00000 - "not a single-group group function


on line

SELECT x.*,SUM(x.quantities) as Tquantities

can you help me to resolve it

CREATE MATERIALIZED VIEW TestView AS
With x AS(
SELECT Numclient as CLIENT,
Numcommand as COMMAND,
count(gender) as quantities
FROM customer,
Command
WHERE Numclient = Numcommand
AND gender =2
GROUP BY Numclient,
Numcommand
),
x1 AS (
SELECT x.*,SUM(x.quantities) as Tquantities
FROM x
)
SELECT x.*,ROUND(x.quantities*100/x1.Tquantities) as Percent
FROM x1, x;

Answer

In order to eliminate error remove x.*, in your original subquery x1. Your select statement can be simplified, like here:

select Numclient CLIENT, Numcommand COMMAND, count(gender) quantities,
       round(100*count(gender)/sum(count(gender)) over()) percent
  from customer 
  join Command on Numclient = Numcommand and gender = 2
  group by Numclient, Numcommand 

SQLFiddle

It's little unclear why are you displaying column COMMAND, when it's equal to CLIENT? I suspect that maybe this is mistake in where condition or this column is superfluous.