CODEWITHSUNDEEP
mysql
Ranjit Kumar
Ranjit Kumar

Reputation: 781

SQL Error (1111): Invalid use of group function

I am trying to fetch data between the (max date in the entrydate column) and (max date in the entrydate column-15days)!!

Table1:Totally it has 953rows enter image description here

Table2: Totally it has 400rows enter image description here

i got this error ,Please help me !!

    Select  
o_material_transaction_inward.Mat_Code,
s_material_details.Mat_Spec,
s_material_details.Mat_Make,
o_material_transaction_inward.Sup_Name,
o_material_transaction_inward.Entry_Date,
o_material_transaction_inward.DC_qty,
o_material_transaction_inward.Received_qty,
    from 
o_material_transaction_inward 
    join 
s_material_details 
    on 
s_material_details.Mat_Code=o_material_transaction_inward.Mat_Code and s_material_details.Mat_Group_Id=o_material_transaction_inward.Mat_Group_id
    where 
o_material_transaction_inward.Entry_Date between Max(o_material_transaction_inward.Entry_Date) and Max(o_material_transaction_inward.Entry_Date - 15)

Upvotes: 0

Views: 9920

Answers (3)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

Update: Fixed BETWEEN predicate:

The reason it wasn't working is that the lowest value need to be the first value in the BETWEEN predicate, so in order to get those rows from the two tables having the entry_date between the max entry date - 15 and max entry date try this:

SELECT
  o.Mat_Code,
  s.Mat_Spec,
  s.Mat_Make,
  o.Sup_Name,
  DATE_FORMAT(o.Entry_Date, '%Y-%m-%d') AS Entry_Date,
  o.DC_qty,
  o.Received_qty
FROM o_material_transaction_inward AS o
INNER JOIN s_material_details      AS s  ON s.Mat_Code     = o.Mat_Code
WHERE o.Entry_Date BETWEEN ((SELECT Max(Entry_Date) 
                            FROM o_material_transaction_inward) - 15)
                       AND (SELECT Max(Entry_Date) 
                            FROM o_material_transaction_inward) ;

SQL Fiddle Demo

Upvotes: 2

user2001117
user2001117

Reputation: 3777

This should work:

SELECT
  o.Mat_Code,
  s.Mat_Spec,
  s.Mat_Make,
  o.Sup_Name,
  o.Entry_Date,
  o.DC_qty,
  o.Received_qty,
FROM o_material_transaction_inward AS o
INNER JOIN s_material_details      AS s  ON s.Mat_Code     = o.Mat_Code
                                        AND s.Mat_Group_Id = o.Mat_Group_id
WHERE o.Entry_Date BETWEEN (SELECT Max(Entry_Date) 
                            FROM o_material_transaction_inward) 
                       AND (SELECT Max(Entry_Date) 
                            FROM o_material_transaction_inward) - 15);

Upvotes: 0

tmuguet
tmuguet

Reputation: 1165

Try:

    Select  
o_material_transaction_inward.Mat_Code,
s_material_details.Mat_Spec,
s_material_details.Mat_Make,
o_material_transaction_inward.Sup_Name,
o_material_transaction_inward.Entry_Date,
o_material_transaction_inward.DC_qty,
o_material_transaction_inward.Received_qty,
    from 
o_material_transaction_inward 
    join 
s_material_details 
    on 
s_material_details.Mat_Code=o_material_transaction_inward.Mat_Code and s_material_details.Mat_Group_Id=o_material_transaction_inward.Mat_Group_id
    having 
o_material_transaction_inward.Entry_Date between Max(o_material_transaction_inward.Entry_Date) and Max(o_material_transaction_inward.Entry_Date - 15)

(see the accepted answer of MySQL: Invalid use of group function for an explanation of where vs having)

Upvotes: 0

Related Questions