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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Subquery

Author  Topic 

junior6202
Starting Member

45 Posts

Posted - 2014-04-24 : 08:48:53
PART WEIGHT Sold
00-PRESS-S 0 17
00-PRESS-S 10 24
00-PRESS-S 16 5
00-Press-S 18 2

SELECT tblShipLines.PART, tblShipLines.WEIGHT, Count(tblShipLines.IDX) AS Sold
FROM tblShipHeader INNER JOIN tblShipLines ON tblShipHeader.IDX = tblShipLines.PARENT
WHERE (((tblShipHeader.SHIP_DATE)>Date()-183))
GROUP BY tblShipLines.PART, tblShipLines.WEIGHT
HAVING (((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 1
Order by Count(tblShipLines.IDX)








How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Go to Top of Page

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?
Go to Top of Page
   

- Advertisement -