Q:

Group by male and female SQL -

I have a sample database and I am trying to find the new vs the returning customers. Below is the link to the sample database which I created:
https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=aa24ab7ce5136ab99a66efbc5414968f
I would like to join the two tables so that I can get the below result:
table picture please have a look
this was my original post in which I wanted to just get the new and the old customers:
https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=e7fbb96a2d269e37d1436e178d523066
The logic is -  is that I am trying to get the new customers for the current period from the transactions table who have not transacted before ever. Then I am calculating the repeating customers who have transacted in the current period but also in the past. Now i would like to get the data in the format which I have attached as a picture. Basically grouping the data as male and female, their count of customers in each bucket, total spend, count of transactions and the quantity they have purchased.
below is the code which I have, but I have one record in the table who is not a new customer but when I run this code it gives me that individual as a new customer:
    select gender, 
case when age < 18 then '<18'
when age between 18 and 24 then '18-24'
when age between 25 and 32 then '25-32'
when age between 33 and 39 then '35-39'
when age between 40 and 46 then '40-46'
when age between 47 and 53 then '46-52'
when age between 54 and 60 then '53-58'
when age > 60 then '61+' end as AgeGroup
, count(distinct individual_id) indiv
, count (distinct transaction_number) txn_count
, sum(dollar_value_us) as Spend
, sum(quantity),
CASE WHEN MIN(txn_date) = min_txn_date THEN 1 ELSE 0 END is_new
from (SELECT 
        DISTINCT a.individual_id, 
        a.dollar_value_us,
        a.txn_date,
        a.quantity,
        a.transaction_number,
        b.gender,
        b.age,
        MIN(txn_date) OVER(PARTITION BY a.individual_id) min_txn_date          
    FROM transaction_detail_mv   a
    join gender_details b on a.individual_id = b.individual_id
    WHERE 
        a.brand_org_code = 'BRAND'
        AND a.is_merch = 1
        AND a.currency_code = 'USD'
        AND a.line_item_amt_type_cd = 'S')

where txn_date >= TO_DATE('10-02-2019', 'DD-MM-YYYY') 
    AND txn_date < TO_DATE('17-02-2019', 'DD-MM-YYYY')

group by gender, 
case when age < 18 then '<18'
when age between 18 and 24 then '18-24'
when age between 25 and 32 then '25-32'
when age between 33 and 39 then '35-39'
when age between 40 and 46 then '40-46'
when age between 47 and 53 then '46-52'
when age between 54 and 60 then '53-58'
when age > 60 then '61+' end,
individual_id,
min_txn_date

The problem with this code is that it is not grouping the age ranges. so if there are 2 Males under 1 age range who are returning customers, it is giving me two records for it, and I need only 1.

A:

The approach to use PL/SQL table was proposed to format the output as per the table definition(https://i.stack.imgur.com/0cjvm.png). Using PL/SQL would help in transforming rows to columns if that was the actual problem statement.
Issue on the query with regards to grouping by age group and returning customer is because "individual_id" was added to the group by clause. Individual_id defines an age and not an age group, adding individual_id in the group by clause creates the result set grouped by "age".Query has been corrected as below,I have done some tests for the described scenario and it works.Please check and confirm if this works or not.
select gender, 
case when age < 18 then '<18'
when age between 18 and 24 then '18-24'
when age between 25 and 32 then '25-32'
when age between 33 and 39 then '35-39'
when age between 40 and 46 then '40-46'
when age between 47 and 53 then '46-52'
when age between 54 and 60 then '53-58'
when age > 60 then '61+' end as AgeGroup
, count(distinct individual_id) indiv 
, count (distinct transaction_number) txn_count
, sum(dollar_value_us) as Spend
, sum(quantity),
CASE WHEN MIN(txn_date) = min_txn_date THEN 1 ELSE 0 END is_new
from (SELECT 
        DISTINCT a.individual_id, 
        a.dollar_value_us,
        a.txn_date,
        a.quantity,
        a.transaction_number,
        b.gender,
        b.age,
        MIN(txn_date) OVER(PARTITION BY a.individual_id) min_txn_date          
    FROM transaction_detail_mv   a
    join gender_details b on a.individual_id = b.individual_id
    WHERE 
        a.brand_org_code = 'BRAND'
        AND a.is_merch = 1
        AND a.currency_code = 'USD'
        AND a.line_item_amt_type_cd = 'S')
where txn_date >= TO_DATE('10-02-2019', 'DD-MM-YYYY') 
    AND txn_date < TO_DATE('17-02-2019', 'DD-MM-YYYY')
group by gender, 
case when age < 18 then '<18'
when age between 18 and 24 then '18-24'
when age between 25 and 32 then '25-32'
when age between 33 and 39 then '35-39'
when age between 40 and 46 then '40-46'
when age between 47 and 53 then '46-52'
when age between 54 and 60 then '53-58'
when age > 60 then '61+' end,
/*individual_id,*/
min_txn_date 

