sql server 2005

Question

Post reply

sql server 2005

hiteshchouhan91

Grasshopper

Points: 12
More actions
June 24, 2012 at 11:54 pm

#260390

select s.part_name as Part_Name,s.part_no as Part_No,s.uom as UOM,i.Stock_OnEntryDate ,sum(i.invoice_quantity) as Import,sum(r.Return_Quantity)as Material_Return,sum(p.Issue_Quantity) as Issue,sum(er.Quantity)as Excess,sum(sr.Quantity) as Short,s.available_quantity as Balance from dri_Stock s left join dri_InwardEntry i on
s.part_no=i.part_no and month(i._date)='6' and year(i._date)='2012' left join dri_ReturnToCustomer r on s.part_no=r.part_no and month(r.date)='6' and year(r.date)='2012' left join dri_IssueToProd p on s.part_no=p.part_no and month(p.date)='6' and year(p.date)='2012' left join dri_ExcessReceipt er on s.Part_No=er.Part_No and month(er.date)='6' and year(er.date)='2012' left join dri_ShortReceipt sr on s.part_no=sr.part_no and month(sr.date)='6' and year(sr.date)='2012' group by s.part_name,s.part_no,s.uom,s.available_quantity
this is my sql query
three are seven table in my query
i am getting error :
"Column 'dri_InwardEntry.Stock_OnEntryDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
i want the i.stock_onentrydate which is entered last in month for perticular s.part_no
and i also it giving me a wrong values of another column eg. if there is two entry in inward_entry table's column invoice_quantity values first entry with 100 and second with 100 it giving me 400
correctly it should give me 200
thank you

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

Hi, Welcome to SSC.

Please follow the second link in my signature on posting code so that we can help you out further.

rhythmk SSCertifiable Points: 7271 More actions · Answer 2

hiteshchouhan91 (6/24/2012)
select s.part_name as Part_Name,s.part_no as Part_No,s.uom as UOM,i.Stock_OnEntryDate ,sum(i.invoice_quantity) as Import,sum(r.Return_Quantity)as Material_Return,sum(p.Issue_Quantity) as Issue,sum(er.Quantity)as Excess,sum(sr.Quantity) as Short,s.available_quantity as Balance from dri_Stock s left join dri_InwardEntry i on s.part_no=i.part_no and month(i._date)='6' and year(i._date)='2012' left join dri_ReturnToCustomer r on s.part_no=r.part_no and month(r.date)='6' and year(r.date)='2012' left join dri_IssueToProd p on s.part_no=p.part_no and month(p.date)='6' and year(p.date)='2012' left join dri_ExcessReceipt er on s.Part_No=er.Part_No and month(er.date)='6' and year(er.date)='2012' left join dri_ShortReceipt sr on s.part_no=sr.part_no and month(sr.date)='6' and year(sr.date)='2012' group by s.part_name,s.part_no,s.uom,s.available_quantity
this is my sql query
three are seven table in my query
i am getting error :
"Column 'dri_InwardEntry.Stock_OnEntryDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
i want the i.stock_onentrydate which is entered last in month for perticular s.part_no
and i also it giving me a wrong values of another column eg. if there is two entry in inward_entry table's column invoice_quantity values first entry with 100 and second with 100 it giving me 400
correctly it should give me 200
thank you

Hi hiteshchouhan91,

Please follow the link suggested by Anthony to post your query.

However from the error I can say for now that include 'Stock_OnEntryDate' column in GROUP BY clause also.

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

thadeushuck Ten Centuries Points: 1367 More actions · Answer 3

IF "Column 'dri_InwardEntry.Stock_OnEntryDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." THEN you probably need to use MAX(dri_InwardEntry.Stock_OnEntryDate) to solve the aggregrate part.

As for ... "giving me a wrong values of another column eg. if there is two entry in inward_entry table's column invoice_quantity values first entry with 100 and second with 100 it giving me 400 correctly it should give me 200" start ripping out joins to other tables not relavent to your base information. Once you get the right answer then make a CTE of that and add in the ripped out joined info to the CTE data.

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Actually if you format this sql it is pretty clear that you did not group by all the non-aggregate columns (I used http://poorsql.com).

SELECT s.part_name AS Part_Name

,s.part_no AS Part_No

,s.uom AS UOM

,i.Stock_OnEntryDate

,sum(i.invoice_quantity) AS Import

,sum(r.Return_Quantity) AS Material_Return

,sum(p.Issue_Quantity) AS Issue

,sum(er.Quantity) AS Excess

,sum(sr.Quantity) AS Short

,s.available_quantity AS Balance

FROM dri_Stock s

LEFT JOIN dri_InwardEntry i ON s.part_no = i.part_no

AND month(i._date) = '6'

AND year(i._date) = '2012'

LEFT JOIN dri_ReturnToCustomer r ON s.part_no = r.part_no

AND month(r.DATE) = '6'

AND year(r.DATE) = '2012'

LEFT JOIN dri_IssueToProd p ON s.part_no = p.part_no

AND month(p.DATE) = '6'

AND year(p.DATE) = '2012'

LEFT JOIN dri_ExcessReceipt er ON s.Part_No = er.Part_No

AND month(er.DATE) = '6'

AND year(er.DATE) = '2012'

LEFT JOIN dri_ShortReceipt sr ON s.part_no = sr.part_no

AND month(sr.DATE) = '6'

AND year(sr.DATE) = '2012'

GROUP BY s.part_name

,s.part_no

,s.uom

,s.available_quantity

You missed s.available_quantity and i.Stock_OnEntryDate

I think you would find far better performance by checking for a date range in your joins instead of all the functions.

ex:

i._date between '6/1/2012' and '7/1/2012'

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323518 More actions · Answer 5

anthony.green (6/25/2012)
Hi, Welcome to SSC.
Please follow the second link in my signature on posting code so that we can help you out further.

careful there! the knee jerk reaction for DDL wasn't needed for a simple syntax issue.

i do the same all the time myself, but sometimes it's not needed.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

hiteshchouhan91 Grasshopper Points: 12 More actions · Answer 6

Thank you for your help,

column import and issue values showing me 2200 and 600

but actual total of these column is 1100 and 300 respectively

i want that it should be give me 1100 and 300

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

hiteshchouhan91 (6/26/2012)
Thank you for your help,
column import and issue values showing me 2200 and 600
but actual total of these column is 1100 and 300 respectively
i want that it should be give me 1100 and 300

Now what Anthony said is totally relevant. I can't possibly be any help without something to work with. I would need ddl for all the objects relevant (create table statements), sample data (insert statements) and desired output. Take a look at the first link in my signature for help in gathering and posting this information.