Trying to change Query - SQL Server Forums

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/

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/

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources