Make query with unique cust_no (1 Viewer)

[bulrush]

I need to run this query, and sum info so there is only a single cus_no. My current query gives me 14,000 records. I need it to be closer to 500.

SELECT dbo_LINHST_SQL.cus_no, Sum(dbo_LINHST_SQL.qty_ordered) AS SumOfqty_ordered, dbo_iminvloc_sql.loc, dbo_CUSFIL_SQL.cus_name, dbo_LINHST_SQL.sls_amt, dbo_CUSFIL_SQL.terr, dbo_CUSFIL_SQL.search_name, dbo_iminvloc_sql.price AS MyPrice INTO tblSalesLastYear
FROM (dbo_CUSFIL_SQL INNER JOIN dbo_LINHST_SQL ON dbo_CUSFIL_SQL.cus_no = dbo_LINHST_SQL.cus_no) LEFT JOIN dbo_iminvloc_sql ON dbo_LINHST_SQL.item_no = dbo_iminvloc_sql.item_no
WHERE (((Mid(Str([billed_dt]),2,4))=2011) AND ((dbo_CUSFIL_SQL.slspsn_no)="30" Or (dbo_CUSFIL_SQL.slspsn_no)="31" Or (dbo_CUSFIL_SQL.slspsn_no)="32" Or (dbo_CUSFIL_SQL.slspsn_no)="33" Or (dbo_CUSFIL_SQL.slspsn_no)="34" Or (dbo_CUSFIL_SQL.slspsn_no)="99") AND ((dbo_CUSFIL_SQL.cus_type_cd) Not Like "NSINA"))
GROUP BY dbo_LINHST_SQL.cus_no, dbo_CUSFIL_SQL.slspsn_no, dbo_iminvloc_sql.loc, dbo_CUSFIL_SQL.cus_name, dbo_LINHST_SQL.sls_amt, dbo_CUSFIL_SQL.terr, dbo_CUSFIL_SQL.search_name, dbo_iminvloc_sql.price
HAVING (((dbo_iminvloc_sql.loc)="01") AND ((dbo_LINHST_SQL.sls_amt)>0) AND ((dbo_CUSFIL_SQL.terr) Not Like "OB"))
ORDER BY dbo_LINHST_SQL.cus_no, Sum(dbo_LINHST_SQL.qty_ordered) DESC , dbo_CUSFIL_SQL.slspsn_no;

Any ideas how to do this?

 

[epon78]

If you group by these fields you will probably get more than one row because the grouping levels are fairly narrow.
loc,
cus_name,
sls_amt,
terr,
search_name,
price

To get a fewer rows, by customer, you will probably have to sum sls_amt, and possibly not use the other categories such as price, name, terr as these will lead to more groups (unless there is only one price, name, terr for each customer).