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
 Trying to change Query

Author  Topic 

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2014-10-17 : 15:19:57
I have this query (below) and it returns one record per customer.
SELECT     CUSTOMER.CSCODE, CUSTOMER.CSNAME, SUM(INVHIST_HDR.IHTOT_AR_AMT) AS TotalSales, CUSTOMER.CSADDR1, CUSTOMER.CSADDR2, 
CUSTOMER.CSADDR3, CUSTOMER.CSCITY, CUSTOMER.CSST, CUSTOMER.CSZIP, CCONTACT.CCTITLE, CCONTACT.CCFNAME, CCONTACT.CCLNAME,
CCONTACT.CCJOBDESC, CCONTACT.CCNAME, CCONTACT.CCADDR1, CCONTACT.CCADDR2, CCONTACT.CCADDR3, CCONTACT.CCCITY, CCONTACT.CCST,
CCONTACT.CCZIP, CCONTACT.CCCOUNTRY, CCONTACT.CCTELENO, CCONTACT.CCFAXNO, CCONTACT.CCEMAILID, SALESAGENT.SANAME
FROM CUSTOMER AS CUSTOMER INNER JOIN
INVHIST_HDR AS INVHIST_HDR ON CUSTOMER.CSCODE = INVHIST_HDR.CSCODE INNER JOIN
CCONTACT ON CUSTOMER.CSCODE = CCONTACT.CSCODE INNER JOIN
SALESAGENT ON CUSTOMER.SACODE = SALESAGENT.SACODE
WHERE (INVHIST_HDR.IHINVOICE_NO > 0) AND (INVHIST_HDR.PLT_NO = 1) AND (INVHIST_HDR.IHINV_DATE >= '09/01/2013') AND
(INVHIST_HDR.IHINV_DATE <= '09/01/2014')
GROUP BY CUSTOMER.CSCODE, CUSTOMER.CSNAME, CUSTOMER.CSADDR1, CUSTOMER.CSADDR2, CUSTOMER.CSADDR3, CUSTOMER.CSCITY, CUSTOMER.CSST,
CUSTOMER.CSZIP, CCONTACT.CCTITLE, CCONTACT.CCFNAME, CCONTACT.CCLNAME, CCONTACT.CCJOBDESC, CCONTACT.CCNAME, CCONTACT.CCADDR1,
CCONTACT.CCADDR2, CCONTACT.CCADDR3, CCONTACT.CCCITY, CCONTACT.CCST, CCONTACT.CCZIP, CCONTACT.CCCOUNTRY, CCONTACT.CCTELENO,
CCONTACT.CCFAXNO, CCONTACT.CCEMAILID, SALESAGENT.SANAME


I want to join another table to the query which would calculate Total Orders for each customer. When I run the below query it is returning one record for each order for the customer. How can I have it just return the Total Orders for each one customer record coming from the other tables?
SELECT     CUSTOMER.CSCODE, CUSTOMER.CSNAME, SUM(INVHIST_HDR.IHTOT_AR_AMT) AS TotalSales, CUSTOMER.CSADDR1, CUSTOMER.CSADDR2, 
CUSTOMER.CSADDR3, CUSTOMER.CSCITY, CUSTOMER.CSST, CUSTOMER.CSZIP, CCONTACT.CCTITLE, CCONTACT.CCFNAME, CCONTACT.CCLNAME,
CCONTACT.CCJOBDESC, CCONTACT.CCNAME, CCONTACT.CCADDR1, CCONTACT.CCADDR2, CCONTACT.CCADDR3, CCONTACT.CCCITY, CCONTACT.CCST,
CCONTACT.CCZIP, CCONTACT.CCCOUNTRY, CCONTACT.CCTELENO, CCONTACT.CCFAXNO, CCONTACT.CCEMAILID, SALESAGENT.SANAME,
COUNT(ORDERS.ORDER_NO) AS TotalOrders
FROM CUSTOMER AS CUSTOMER INNER JOIN
INVHIST_HDR AS INVHIST_HDR ON CUSTOMER.CSCODE = INVHIST_HDR.CSCODE INNER JOIN
CCONTACT ON CUSTOMER.CSCODE = CCONTACT.CSCODE INNER JOIN
SALESAGENT ON CUSTOMER.SACODE = SALESAGENT.SACODE INNER JOIN
ORDERS ON CUSTOMER.CSCODE = ORDERS.CSCODE
WHERE (INVHIST_HDR.IHINVOICE_NO > 0) AND (INVHIST_HDR.PLT_NO = 1) AND (INVHIST_HDR.IHINV_DATE >= '09/01/2013') AND
(INVHIST_HDR.IHINV_DATE <= '09/01/2014')
GROUP BY CUSTOMER.CSCODE, CUSTOMER.CSNAME, CUSTOMER.CSADDR1, CUSTOMER.CSADDR2, CUSTOMER.CSADDR3, CUSTOMER.CSCITY, CUSTOMER.CSST,
CUSTOMER.CSZIP, CCONTACT.CCTITLE, CCONTACT.CCFNAME, CCONTACT.CCLNAME, CCONTACT.CCJOBDESC, CCONTACT.CCNAME, CCONTACT.CCADDR1,
CCONTACT.CCADDR2, CCONTACT.CCADDR3, CCONTACT.CCCITY, CCONTACT.CCST, CCONTACT.CCZIP, CCONTACT.CCCOUNTRY, CCONTACT.CCTELENO,
CCONTACT.CCFAXNO, CCONTACT.CCEMAILID, SALESAGENT.SANAME, ORDERS.ORDER_NO


Hope that makes sense.
Thanks,
Stacy Walker

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-17 : 15:25:55
Please edit your post to format the first query so that it isn't one very long line in here.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-17 : 15:26:39
Please also provide sample data that shows the current issue and then the expected output using that same sample data.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2014-10-17 : 16:13:07
Sorry I screwed up my first query is returning the customer multiple times because I am also pulling the contacts. The query takes forever if I try to calculate the total orders from another table for each customer.

Is there a way to do a second query using the query results of the first table to join to a new table to count orders by customer?

Not sure if that makes sense.

Basically what I want is to pull customers and contacts between a certain date range which my first query does. On top of that I want to count all of the customers orders from the order table and return the total during a date range. Do I need to do separate queries or is there a way to do all of this in one query?

Thanks,
Stacy
Go to Top of Page
   

- Advertisement -