Counting values using nested SQL query

Question

I have the following tables with values:

CREATE TABLE Produkty 
(
    Id NUMBER(10) NOT NULL,
    Nazwa VARCHAR2(50) NOT NULL,
    Status VARCHAR2(50) NOT NULL,
    CONSTRAINT CHK_Status CHECK (Status IN ('czynny', 'zakonczony')),
    CONSTRAINT PK_Id PRIMARY KEY (Id)
);

INSERT INTO Produkty VALUES (1, 'Koszula', 'czynny');
INSERT INTO Produkty VALUES (2, 'Buty', 'zakonczony');
INSERT INTO Produkty VALUES (3, 'Krawat', 'czynny');
INSERT INTO Produkty VALUES (4, 'Spodnie', 'zakonczony');
INSERT INTO Produkty VALUES (5, 'Sznurówki', 'czynny');
INSERT INTO Produkty VALUES (6, 'Kamizelka', 'zakonczony');
INSERT INTO Produkty VALUES (7, 'Marynarka', 'czynny');
INSERT INTO Produkty VALUES (8, 'Czapka', 'zakonczony');

CREATE TABLE Fundusze 
(
    Id NUMBER(10) NOT NULL,
    Nazwa VARCHAR2(50) NOT NULL,
    Waluta VARCHAR2(50) NOT NULL,
    Typ VARCHAR2(50) NOT NULL,
    CONSTRAINT CHK_StatusFundusze CHECK (Typ IN ('defensywny', 'akcyjny', 'oszczędnościowy')),
    CONSTRAINT PK_IdFundusze PRIMARY KEY (Id),
);

INSERT INTO Fundusze VALUES (1, 'Def1', 'PLN', 'defensywny');
INSERT INTO Fundusze VALUES (2, 'Def2', 'USD', 'defensywny');
INSERT INTO Fundusze VALUES (3, 'Akc1', 'PLN', 'akcyjny');
INSERT INTO Fundusze VALUES (4, 'Akc2', 'USD', 'akcyjny');
INSERT INTO Fundusze VALUES (5, 'Os1', 'PLN', 'oszczędnościowy');
INSERT INTO Fundusze VALUES (6, 'Os2', 'USD', 'oszczędnościowy');

CREATE TABLE Fundusze_produktow 
(
    Pr_Id NUMBER(10) NOT NULL,
    Fun_Id NUMBER(10) NOT NULL,
    Data_od DATE,
    Data_do DATE,
    Oplata NUMBER(20),
    CONSTRAINT fk_produkty FOREIGN KEY (Pr_Id) REFERENCES Produkty(Id),
    CONSTRAINT fk_fundusze FOREIGN KEY (Fun_Id) REFERENCES Fundusze(Id),
    CONSTRAINT PK_Fundusze_produktow PRIMARY KEY (Pr_Id, Fun_Id)
);

INSERT INTO Fundusze_produktow VALUES (1, 1, '12/17/1980', '12/20/1980', 11);
INSERT INTO Fundusze_produktow VALUES (1, 2, '12/17/1980', '12/20/1980', 12);
INSERT INTO Fundusze_produktow VALUES (1, 3, '12/17/1980', '12/20/1980', 9);
INSERT INTO Fundusze_produktow VALUES (1, 4, '12/17/1980', '12/20/1980', 13);
INSERT INTO Fundusze_produktow VALUES (1, 5, '12/17/1980', '12/20/1980', 15);
INSERT INTO Fundusze_produktow VALUES (1, 6, '12/17/1980', '12/20/1980', 17);
INSERT INTO Fundusze_produktow VALUES (2, 2, '12/17/1980', '12/20/1980', 14);
INSERT INTO Fundusze_produktow VALUES (2, 4, '12/17/1980', '12/20/1980', 18);
INSERT INTO Fundusze_produktow VALUES (2, 6, '12/17/1980', '12/20/1980', 19);
INSERT INTO Fundusze_produktow VALUES (3, 2, '12/17/1980', '12/20/1980', 12);
INSERT INTO Fundusze_produktow VALUES (4, 2, '12/17/1980', '12/20/1980', 12);
INSERT INTO Fundusze_produktow VALUES (5, 2, '12/17/1980', '12/20/1980', 16);
INSERT INTO Fundusze_produktow VALUES (6, 2, '12/17/1980', '12/20/1980', 8);
INSERT INTO Fundusze_produktow VALUES (6, 4, '12/17/1980', '12/20/1980', 9);
INSERT INTO Fundusze_produktow VALUES (7, 2, '12/17/1980', '12/20/1980', 11);
INSERT INTO Fundusze_produktow VALUES (7, 3, '12/17/1980', '12/20/1980', 13);

I'm now trying to fetch Produkty with status 'czynny' and a number of Fundusze with type 'akcyjny', which are associated with them. I use Oracle Apex and run the following query:

SELECT 
    COUNT(*), z.fun_id, p.nazwa  
FROM 
    Produkty p, Fundusze_produktow z 
WHERE 
    p.Status = 'czynny' 
    AND z.fun_id IN (SELECT id FROM Fundusze 
                     WHERE Typ = 'akcyjny') 
    AND z.pr_id = p.id 
GROUP BY 
    z.fun_id, p.nazwa;

It returns correctly the nazwa from Produkty and associated z.fun_id (which in my case is 3 and 4), but it doesn't count them properly:

COUNT(*)	FUN_ID	NAZWA
1	3	Koszula
1	4	Koszula
1	3	Marynarka

I tried everything and couldn't get the correct result.

Thanks for help.

Please show us the results you are expecting, with correct counting. — Dale K
– Dale K, Commented Sep 8 at 19:46
Try count by count(*)over(partition by fun_id) or count(*)over(partition by nazwa). — ValNik
– ValNik, Commented Sep 8 at 20:21

Yaksh · Accepted Answer · 2025-09-08 19:03:54Z

2

You are grouping by both fun_id and nazwa, so the count is per fund-product pair, not per product.

To count how many akcyjny fundusze are linked to each czynny product, group only by the product name:

SELECT 
    p.Nazwa,
    COUNT(DISTINCT z.Fun_Id) AS Liczba_Akcyjnych_Funduszy
FROM 
    Produkty p
JOIN 
    Fundusze_produktow z ON z.Pr_Id = p.Id
JOIN 
    Fundusze f ON f.Id = z.Fun_Id
WHERE 
    p.Status = 'czynny'
    AND f.Typ = 'akcyjny'
GROUP BY 
    p.Nazwa;

This gives one row per product with the correct count of associated akcyjny funds.

answered Sep 8 at 19:03

Yaksh

704 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

aretai Sep 9 at 8:08

Yes that's the correct answer. It worked. Thanks

Collectives™ on Stack Overflow

Counting values using nested SQL query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related