May 2, 2012 at 3:22 pm
Hi guys forgive me if this is simple but I seem to need more complex query the more my c# app progresses and being a new to both 1 out ways the other.
I am trying get the max number and the min number of a column and sum the answer
SELECT Id, Date_Used, Engineer, Machine, Module, Qty, Part_Number, Part_Description, Cost, Status, Count, Con1, Con2, Con3, Con4, Con5, Con6, Con7, Con8, Con9, Con10 FROM dbo.PartsUsedTB
count being the column in question. I am stuck 🙁
Any advice please ?
jay
May 2, 2012 at 3:46 pm
Hi,
I do not know if I understand correctly but try this:
SELECT
MIN(Count) as MinCount,
MAX(Count) as MaxCount,
(MIN(Count) + MAX(Count)) as SumCount
FROM dbo.PartsUsedTB
Hope this helps.
May 3, 2012 at 3:39 am
May 3, 2012 at 8:08 am
jerome.morris (5/2/2012)
Hi guys forgive me if this is simple but I seem to need more complex query the more my c# app progresses and being a new to both 1 out ways the other.I am trying get the max number and the min number of a column and sum the answer
SELECT Id, Date_Used, Engineer, Machine, Module, Qty, Part_Number, Part_Description, Cost, Status, Count, Con1, Con2, Con3, Con4, Con5, Con6, Con7, Con8, Con9, Con10 FROM dbo.PartsUsedTB
count being the column in question. I am stuck 🙁
Any advice please ?
jay
Looks like the query posted should work. Since you say you are new I would like to offer some free advice.
You have a column in your table (COUNT), that is a reserved word in SQL. While it will work it will cause you nothing but pain.
It appears you have added TB to the end of the table name to indicate it is a table. This is generally not considered best practice. Just name your tables by what they represent.
It is generally not considered good practice to have a generic ID as a primary key. Some people would disagree that there is nothing wrong with using an identity as a primary key but I think it is fine a lot of the time. The reason you don't want to have your key be so generic as ID is because it is too vague. Remember too that Foreign keys should keep the same name. I would suggest changing this to something like Parts_Used_ID.
Last bit of advice, which is the one I hope you listen to the most, is your data does not appear to be normalized. We do not use multiple columns to hold multiple like values. I don't know what Con1...Con10 represent but you need to break that out into a new table. Otherwise you will have to change your table and all your queries when you need to add an 11th.
_______________________________________________________________
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/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply