Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
junior6202
Starting Member
45 Posts |
Posted - 2014-04-24 : 08:48:53
|
PART WEIGHT Sold00-PRESS-S 0 1700-PRESS-S 10 2400-PRESS-S 16 500-Press-S 18 2SELECT tblShipLines.PART, tblShipLines.WEIGHT, Count(tblShipLines.IDX) AS SoldFROM tblShipHeader INNER JOIN tblShipLines ON tblShipHeader.IDX = tblShipLines.PARENTWHERE (((tblShipHeader.SHIP_DATE)>Date()-183))GROUP BY tblShipLines.PART, tblShipLines.WEIGHTHAVING (((tblShipLines.PART)="00-6QTPRESS-S"));what would you recommend to use to find out which Weight was Sold the most. Im having trouble coming up with the subquery. |
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2014-04-24 : 09:10:48
|
Top 1Order by Count(tblShipLines.IDX)How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-04-24 : 09:11:55
|
First, remove all the extra parentheses around the WHERE and HAVING clauses. Then, get rid of the HAVING clause and move the condition to the WHERE clause e.g.WHERE tblShipHeader.SHIP_DATE >Date()-183 AND tblShipLines.PART="00-6QTPRESS-S" Also, what is Date()? That's not a SQL Server built-in function. Do you mean GETDATE()?Could you please post the DDL for the tables involved in the query and some DML to populate the tables with sample data, along with your expected results? |
|
|
|
|
|