After adding column in select and group by i am getting extra rows

Mark Tabor

select distinct 'carTypes' as type, mi.WOMENNAME as "mother name", mi.womencnic as "mother CNIC", '' as BFORMNO, '' as child_gender, mi.PROVINCE, mi.district, mi.tehsil, mi.HUSBANDNAME, mi.PHONENO contact, mi.address, SUM(p.amount) paid_amount

from MINFORMATION mi

inner join PAYMENTINFORMATION p on p.BENEFICIARYID=mi.WOMENID and p.BANKSTATUS='Successfull'

where mi.month IS NOT NULL

and p.generationdate between '24-JUN-21' and '01-JAN-22'

group by mi.WOMENNAME, mi.womencnic,mi.PROVINCE, mi.district, mi.tehsil, mi.HUSBANDNAME, mi.PHONENO , mi.address

this is my query I need generationDate in select list as well which is coming from PAYMENTINFORMATION tableso after adding it in select list it ask me to add it in group by as well by doing so I am getting extra row my actual rows are 1629 but after adding generationDate in select and order by I am getting 1944 Rows how to solve it its oracle query

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Mark Tabor4y ago
Thanks imran I am already having it in that manner TO_DATE(p.GENERATIONDATE,'DD-MON-YY')
and in order by i have TO_DATE(p.GENERATIONDATE,'DD-MON-YY') AS GEN_DATE
Muhammad Imran Ansari4y ago
Chech the datatype of generationDate column, it is only date or datetime. Check the following query:
SELECT DISTINCT 'carTypes' as type, mi.WOMENNAME as "mother name", mi.womencnic as "mother CNIC", '' as BFORMNO, '' as child_gender, mi.PROVINCE, mi.district, mi.tehsil, mi.HUSBANDNAME, mi.PHONENO contact, mi.address, SUM(p.amount) paid_amount, CAST(generationDate AS DATE)
FROM MINFORMATION mi
    INNER JOIN PAYMENTINFORMATION p on p.BENEFICIARYID=mi.WOMENID and p.BANKSTATUS='Successfull'
WHERE mi.month IS NOT NULL
AND p.generationdate BETWEEN '24-JUN-21' AND '01-JAN-22'
GROUP BY  mi.WOMENNAME, mi.womencnic,mi.PROVINCE, mi.district, mi.tehsil, mi.HUSBANDNAME, mi.PHONENO , mi.address, CAST(generationDate AS DATE)