Query report with distinct value

#1

i have tried using ‘distinct’, but its not working.

in material request, Item code will repeated when we put Material Request. so when create report, i don’t want repeated Item code, i want once. so in order to remove repeated Item code in report, i used ‘DISTINCT’ sql query but its not working:

select distinct
tabMaterial Request Item.item_code as “Item Code:100”,
tabMaterial Request Item.item_name as “Item name:140”,
tabMaterial Request.creation_date as “MR Date:100”

from
tabMaterial Request Item, tabMaterial Request

where
tabMaterial Request Item.parent = tabMaterial Request.name
and tabMaterial Request Item.docstatus = 0
order by tabMaterial Request Item.name asc

#2

Don’t use DISTINCT use GROUP BY
Also, don’t use table names, instead use aliases for tables. You can write something like this:

Also, what is the purpose of this report ? Does any default report not satisfy your purpose ?

#3

@root13F, thanks for your help, i need this report for how much time taken when we buy Material Item. and to fine the Average time for item.
For Example, we buy 1 item many time in different months. so i want to fine the average time of that particular item. Time is from Material Request to Purchase Receipt. i want to add all time that taken when we buy that particular item and need ti fine Average time, So later when we put Material Requestion, we will know that how much that particular item will take time to receipt .