jakebur01 Posted July 15, 2011 Share Posted July 15, 2011 I am joining two tables together. Customer Master and Customer Sales By Class. The Customer Class table is filled with customer numbers, categories, and sales periods. I would like the total of the sales periods and categories (YKP,YKU,YKT). If the the total of the periods and three categories for the customer is >= 500 it will grab the row. Thanks, Jake PERIODS sa_cust_item_cls.SALES_OLDEST_PD sa_cust_item_cls.SALES_PD_2 sa_cust_item_cls.SALES_PD_3 sa_cust_item_cls.SALES_PD_4 sa_cust_item_cls.SALES_PD_5 sa_cust_item_cls.SALES_PD_6 sa_cust_item_cls.SALES_PD_7 sa_cust_item_cls.SALES_PD_8 sa_cust_item_cls.SALES_PD_9 sa_cust_item_cls.SALES_PD_10 sa_cust_item_cls.SALES_PD_11 sa_cust_item_cls.SALES_PD_12 sa_cust_item_cls.SALES_PD_13 sa_cust_item_cls.SALES_PD_14 sa_cust_item_cls.SALES_PD_15 sa_cust_item_cls.SALES_PD_16 sa_cust_item_cls.SALES_PD_17 sa_cust_item_cls.SALES_PD_18 sa_cust_item_cls.SALES_PD_19 sa_cust_item_cls.SALES_PD_20 sa_cust_item_cls.SALES_PD_21 sa_cust_item_cls.SALES_PD_22 sa_cust_item_cls.SALES_PD_23 sa_cust_item_cls.SALES_PD_24 sa_cust_item_cls.SALES_PD_25 sa_cust_item_cls.SALES_LAST_PD_26 sa_cust_item_cls.SALES_CURR CURRENT QUERY SO FAR SELECT * FROM ar_cust_mast LEFT JOIN ar_ship_to_addr ON ar_cust_mast.CUSTOMER_NUM = ar_ship_to_addr.CUSTOMER_NUM WHERE (sa_cust_item_cls.ITEM_CLASS = 'YKP' or sa_cust_item_cls.ITEM_CLASS = 'YKU'or sa_cust_item_cls.ITEM_CLASS = 'YKT') AND ar_cust_mast.ACTIVE='Y' AND ar_cust_mast.CUSTOMER_CLASS<>'RET' AND ar_cust_mast.CUSTOMER_CLASS<>'REF' AND ar_cust_mast.CUSTOMER_CLASS<>'GCA' AND ar_cust_mast.CUSTOMER_CLASS<>'OUT' AND ar_cust_mast.CUSTOMER_CLASS<>'REC' AND ar_cust_mast.CUSTOMER_CLASS<>'SSC' AND ar_cust_mast.CUSTOMER_CLASS<>'WHS' AND ar_cust_mast.CUSTOMER_CLASS<>'SDD'; Quote Link to comment Share on other sites More sharing options...
jakebur01 Posted July 15, 2011 Author Share Posted July 15, 2011 This should have said I am joining Customer Mater and Customer Ship Address Table. The Customer Class table is the one that has all the periods in it. I am joining two tables together. Customer Master and Customer Sales By Class. The Customer Class table is filled with customer numbers, categories, and sales periods. I would like the total of the sales periods and categories (YKP,YKU,YKT). If the the total of the periods and three categories for the customer is >= 500 it will grab the row. Thanks, Jake PERIODS sa_cust_item_cls.SALES_OLDEST_PD sa_cust_item_cls.SALES_PD_2 sa_cust_item_cls.SALES_PD_3 sa_cust_item_cls.SALES_PD_4 sa_cust_item_cls.SALES_PD_5 sa_cust_item_cls.SALES_PD_6 sa_cust_item_cls.SALES_PD_7 sa_cust_item_cls.SALES_PD_8 sa_cust_item_cls.SALES_PD_9 sa_cust_item_cls.SALES_PD_10 sa_cust_item_cls.SALES_PD_11 sa_cust_item_cls.SALES_PD_12 sa_cust_item_cls.SALES_PD_13 sa_cust_item_cls.SALES_PD_14 sa_cust_item_cls.SALES_PD_15 sa_cust_item_cls.SALES_PD_16 sa_cust_item_cls.SALES_PD_17 sa_cust_item_cls.SALES_PD_18 sa_cust_item_cls.SALES_PD_19 sa_cust_item_cls.SALES_PD_20 sa_cust_item_cls.SALES_PD_21 sa_cust_item_cls.SALES_PD_22 sa_cust_item_cls.SALES_PD_23 sa_cust_item_cls.SALES_PD_24 sa_cust_item_cls.SALES_PD_25 sa_cust_item_cls.SALES_LAST_PD_26 sa_cust_item_cls.SALES_CURR CURRENT QUERY SO FAR SELECT * FROM ar_cust_mast LEFT JOIN ar_ship_to_addr ON ar_cust_mast.CUSTOMER_NUM = ar_ship_to_addr.CUSTOMER_NUM WHERE (sa_cust_item_cls.ITEM_CLASS = 'YKP' or sa_cust_item_cls.ITEM_CLASS = 'YKU'or sa_cust_item_cls.ITEM_CLASS = 'YKT') AND ar_cust_mast.ACTIVE='Y' AND ar_cust_mast.CUSTOMER_CLASS<>'RET' AND ar_cust_mast.CUSTOMER_CLASS<>'REF' AND ar_cust_mast.CUSTOMER_CLASS<>'GCA' AND ar_cust_mast.CUSTOMER_CLASS<>'OUT' AND ar_cust_mast.CUSTOMER_CLASS<>'REC' AND ar_cust_mast.CUSTOMER_CLASS<>'SSC' AND ar_cust_mast.CUSTOMER_CLASS<>'WHS' AND ar_cust_mast.CUSTOMER_CLASS<>'SDD'; Quote Link to comment Share on other sites More sharing options...
fenway Posted July 15, 2011 Share Posted July 15, 2011 Sorry, you want to add what? Quote Link to comment Share on other sites More sharing options...
jakebur01 Posted July 15, 2011 Author Share Posted July 15, 2011 This is what I am wanting to add to my main query. Where this total is greater than 500 it will retrieve the rows. SELECT sa_cust_item_cls.SALES_OLDEST_PD + sa_cust_item_cls.SALES_PD_2 + sa_cust_item_cls.SALES_PD_3 + sa_cust_item_cls.SALES_PD_4 + sa_cust_item_cls.SALES_PD_5 + sa_cust_item_cls.SALES_PD_6 + sa_cust_item_cls.SALES_PD_7 + sa_cust_item_cls.SALES_PD_8 + sa_cust_item_cls.SALES_PD_9 + sa_cust_item_cls.SALES_PD_10 + sa_cust_item_cls.SALES_PD_11 + sa_cust_item_cls.SALES_PD_12 + sa_cust_item_cls.SALES_PD_13 + sa_cust_item_cls.SALES_PD_14 + sa_cust_item_cls.SALES_PD_15 + sa_cust_item_cls.SALES_PD_16 + sa_cust_item_cls.SALES_PD_17 + sa_cust_item_cls.SALES_PD_18 + sa_cust_item_cls.SALES_PD_19 + sa_cust_item_cls.SALES_PD_20 + sa_cust_item_cls.SALES_PD_21 + sa_cust_item_cls.SALES_PD_22 + sa_cust_item_cls.SALES_PD_23 + sa_cust_item_cls.SALES_PD_24 + sa_cust_item_cls.SALES_PD_25 + sa_cust_item_cls.SALES_LAST_PD_26 + sa_cust_item_cls.SALES_CURR AS period_totals WHERE (sa_cust_item_cls.ITEM_CLASS = 'YKP' or sa_cust_item_cls.ITEM_CLASS = 'YKU'or sa_cust_item_cls.ITEM_CLASS = 'YKT') AND ar_cust_mast.CUSTOMER_NUM = sa_cust_item_cls.CUSTOMER_NUM Main Query SELECT * FROM ar_cust_mast LEFT JOIN ar_ship_to_addr ON ar_cust_mast.CUSTOMER_NUM = ar_ship_to_addr.CUSTOMER_NUM WHERE (sa_cust_item_cls.ITEM_CLASS = 'YKP' or sa_cust_item_cls.ITEM_CLASS = 'YKU'or sa_cust_item_cls.ITEM_CLASS = 'YKT') AND ar_cust_mast.ACTIVE='Y' AND ar_cust_mast.CUSTOMER_CLASS<>'RET' AND ar_cust_mast.CUSTOMER_CLASS<>'REF' AND ar_cust_mast.CUSTOMER_CLASS<>'GCA' AND ar_cust_mast.CUSTOMER_CLASS<>'OUT' AND ar_cust_mast.CUSTOMER_CLASS<>'REC' AND ar_cust_mast.CUSTOMER_CLASS<>'SSC' AND ar_cust_mast.CUSTOMER_CLASS<>'WHS' AND ar_cust_mast.CUSTOMER_CLASS<>'SDD'; Basically I only want the customers whose sales in categories YKU,YKT,and YKP are greater than 500 for the total of the sales periods. And I am appending the shipping address table so I can get the customers shipping addresses in the process. Quote Link to comment Share on other sites More sharing options...
fenway Posted July 15, 2011 Share Posted July 15, 2011 And when you tried to add this to the where clause, what happened? Quote Link to comment Share on other sites More sharing options...
jakebur01 Posted July 15, 2011 Author Share Posted July 15, 2011 I have not added it yet. I am trying to figure it out. How can I add the columns of the three categories together per customer and then determine whether it is selected or not based on if the complete total is 500 or more? Quote Link to comment Share on other sites More sharing options...
jakebur01 Posted July 15, 2011 Author Share Posted July 15, 2011 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE (sa_cust_item_cls.ITEM_CLASS = 'YKP' or sa_cust_item_cls.ITEM_CLASS = 'YKU' at line 15 SELECT * FROM ar_cust_mast LEFT JOIN ar_ship_to_addr ON ar_cust_mast.CUSTOMER_NUM = ar_ship_to_addr.CUSTOMER_NUM LEFT JOIN sa_cust_item_cls ON ar_cust_mast.CUSTOMER_NUM = sa_cust_item_cls.CUSTOMER_NUM WHERE (sa_cust_item_cls.ITEM_CLASS = 'YKP' or sa_cust_item_cls.ITEM_CLASS = 'YKU'or sa_cust_item_cls.ITEM_CLASS = 'YKT') AND ar_cust_mast.ACTIVE='Y' AND ar_cust_mast.CUSTOMER_CLASS<>'RET' AND ar_cust_mast.CUSTOMER_CLASS<>'REF' AND ar_cust_mast.CUSTOMER_CLASS<>'GCA' AND ar_cust_mast.CUSTOMER_CLASS<>'OUT' AND ar_cust_mast.CUSTOMER_CLASS<>'REC' AND ar_cust_mast.CUSTOMER_CLASS<>'SSC' AND ar_cust_mast.CUSTOMER_CLASS<>'WHS' AND ar_cust_mast.CUSTOMER_CLASS<>'SDD' AND (SELECT sa_cust_item_cls.SALES_OLDEST_PD + sa_cust_item_cls.SALES_PD_2 + sa_cust_item_cls.SALES_PD_3 + sa_cust_item_cls.SALES_PD_4 + sa_cust_item_cls.SALES_PD_5 + sa_cust_item_cls.SALES_PD_6 + sa_cust_item_cls.SALES_PD_7 + sa_cust_item_cls.SALES_PD_8 + sa_cust_item_cls.SALES_PD_9 + sa_cust_item_cls.SALES_PD_10 + sa_cust_item_cls.SALES_PD_11 + sa_cust_item_cls.SALES_PD_12 + sa_cust_item_cls.SALES_PD_13 + sa_cust_item_cls.SALES_PD_14 + sa_cust_item_cls.SALES_PD_15 + sa_cust_item_cls.SALES_PD_16 + sa_cust_item_cls.SALES_PD_17 + sa_cust_item_cls.SALES_PD_18 + sa_cust_item_cls.SALES_PD_19 + sa_cust_item_cls.SALES_PD_20 + sa_cust_item_cls.SALES_PD_21 + sa_cust_item_cls.SALES_PD_22 + sa_cust_item_cls.SALES_PD_23 + sa_cust_item_cls.SALES_PD_24 + sa_cust_item_cls.SALES_PD_25 + sa_cust_item_cls.SALES_LAST_PD_26 + sa_cust_item_cls.SALES_CURR AS period_totals WHERE (sa_cust_item_cls.ITEM_CLASS = 'YKP' or sa_cust_item_cls.ITEM_CLASS = 'YKU'or sa_cust_item_cls.ITEM_CLASS = 'YKT') AND ar_cust_mast.CUSTOMER_NUM = sa_cust_item_cls.CUSTOMER_NUM) period_totals >= '500'; Quote Link to comment Share on other sites More sharing options...
fenway Posted July 15, 2011 Share Posted July 15, 2011 Per customer? I'm confused -- I didn't see a GROUP BY. Please show sample output for the original query. Quote Link to comment Share on other sites More sharing options...
jakebur01 Posted July 15, 2011 Author Share Posted July 15, 2011 cust_mast.CUSTOMER_NUM is the customer number column. Each table has a column with the customer number in it. However, when getting the totals, the customer's number may be listed in sa_cust_item_cls three times since their are three categories per customer. customer1 'YKT' sales period 1 sales period 2 sales period 3 etc... customer1 'YKP' sales period 1 sales period 2 sales period 3 etc... customer1 'YKU' sales period 1 sales period 2 sales period 3 etc... customer2 'YKT' sales period 1 sales period 2 sales period 3 etc... customer2 'YKP' sales period 1 sales period 2 sales period 3 etc... customer2 'YKU' sales period 1 sales period 2 sales period 3 etc... I want the total of all the sales periods and all three categories per customer. If the customers total sales for those three categories are not >=500, then I do not want it to select the customers information I want it to only select the customers whose sales for those three categories are 500 or greater. Quote Link to comment Share on other sites More sharing options...
jakebur01 Posted July 15, 2011 Author Share Posted July 15, 2011 ar_cust_mast.COMPANY ar_cust_mast.CUSTOMER_NUM ar_cust_mast.CUSTOMER_NAME ar_cust_mast.ADDRESS_1 ar_cust_mast.ADDRESS_2 ar_cust_mast.ADDRESS_3 ar_cust_mast.CITY ar_cust_mast.STATE ar_cust_mast.ZIP_CODE ar_cust_mast.PHONE_1 ar_cust_mast.PHONE_2 ar_cust_mast.CONTACT_1 ar_cust_mast.CONTACT_2 ar_cust_mast.EMAIL_1 ar_cust_mast.EMAIL_2 ar_cust_mast.WEBSITE ar_cust_mast.ALPHA_SORT_KEY ar_cust_mast.NORMAL_SHIP_TO ar_cust_mast.CUSTOMER_CLASS ar_cust_mast.SLSP_TERR ar_cust_mast.TERMS_CODE ar_cust_mast.TAX_CODE ar_cust_mast.TAX_TYPE ar_cust_mast.PRICE_LEVEL ar_cust_mast.GEOGRAPHY_CODE ar_cust_mast.GL_TABLE ar_cust_mast.MTD_SALES_AMT ar_cust_mast.MTD_COST_AMT ar_cust_mast.YTD_SALES_AMT ar_cust_mast.YTD_COST_AMT ar_cust_mast.PYR_SALES_AMT ar_cust_mast.PYR_COST_AMT ar_cust_mast.BALANCE ar_cust_mast.HIGH_BALANCE ar_cust_mast.CREDIT_LIMIT ar_cust_mast.SITE_CHARGE ar_cust_mast.MESSAGE ar_cust_mast.ON_HOLD ar_cust_mast.STORE_SO_SALES ar_cust_mast.BACKORDER_FLAG ar_cust_mast.STATEMENT_CODE ar_cust_mast.STORE_SO_INVOICE ar_cust_mast.STORE_PAYMENT ar_cust_mast.RECEIVE_DUN ar_cust_mast.LAST_DUN_SENT ar_cust_mast.SVC_CHG_GRACE_PD ar_cust_mast.COMM_PCT ar_cust_mast.OFF_INV_DISC_PCT ar_cust_mast.ROUTE ar_cust_mast.LEDGERCARD_SEQ ar_cust_mast.USE_LEDGERCARDS ar_cust_mast.DEFAULT_BRANCH ar_cust_mast.CREDIT_CHECK ar_cust_mast.INCLUDE_INV ar_cust_mast.ACTIVE ar_cust_mast.NOT_USED_2 ar_cust_mast.PO_REQUIRED ar_cust_mast.UPS_GROUND_CODE ar_cust_mast.CUST_PRICE_CLASS ar_cust_mast.PICK_TKT_PRICES ar_cust_mast.PRINT_CUST_ITEM ar_cust_mast.NOT_USED_3 ar_cust_mast.ASSIGNED_DATE ar_cust_mast.LEAD_SOURCE ar_cust_mast.OPENED_DATE ar_cust_mast.LAST_SALE_DATE ar_cust_mast.LAST_PMT_DATE ar_cust_mast.HIGH_BAL_DATE ar_cust_mast.LAST_DUN_DATE ar_cust_mast.TAX_EXEMPT_NUM ar_cust_mast.SHIP_VIA ar_cust_mast.UPS_ZONE ar_ship_to_addr.COMPANY ar_ship_to_addr.CUSTOMER_NUM ar_ship_to_addr.SHIP_TO ar_ship_to_addr.SHIP_TO_NAME ar_ship_to_addr.SHIP_TO_ADDR_1 ar_ship_to_addr.SHIP_TO_ADDR_2 ar_ship_to_addr.SHIP_TO_ADDR_3 ar_ship_to_addr.CITY ar_ship_to_addr.STATE ar_ship_to_addr.ZIP_CODE sa_cust_item_cls.COMPANY sa_cust_item_cls.CUSTOMER_NUM sa_cust_item_cls.ITEM_CLASS sa_cust_item_cls.SHIP_TO sa_cust_item_cls.SALES_OLDEST_PD sa_cust_item_cls.SALES_PD_2 sa_cust_item_cls.SALES_PD_3 sa_cust_item_cls.SALES_PD_4 sa_cust_item_cls.SALES_PD_5 sa_cust_item_cls.SALES_PD_6 sa_cust_item_cls.SALES_PD_7 sa_cust_item_cls.SALES_PD_8 sa_cust_item_cls.SALES_PD_9 sa_cust_item_cls.SALES_PD_10 sa_cust_item_cls.SALES_PD_11 sa_cust_item_cls.SALES_PD_12 sa_cust_item_cls.SALES_PD_13 sa_cust_item_cls.SALES_PD_14 sa_cust_item_cls.SALES_PD_15 sa_cust_item_cls.SALES_PD_16 sa_cust_item_cls.SALES_PD_17 sa_cust_item_cls.SALES_PD_18 sa_cust_item_cls.SALES_PD_19 sa_cust_item_cls.SALES_PD_20 sa_cust_item_cls.SALES_PD_21 sa_cust_item_cls.SALES_PD_22 sa_cust_item_cls.SALES_PD_23 sa_cust_item_cls.SALES_PD_24 sa_cust_item_cls.SALES_PD_25 sa_cust_item_cls.SALES_LAST_PD_26 sa_cust_item_cls.SALES_CURR sa_cust_item_cls.COST_OLDEST_PD sa_cust_item_cls.COST_PD_2 sa_cust_item_cls.COST_PD_3 sa_cust_item_cls.COST_PD_4 sa_cust_item_cls.COST_PD_5 sa_cust_item_cls.COST_PD_6 sa_cust_item_cls.COST_PD_7 sa_cust_item_cls.COST_PD_8 sa_cust_item_cls.COST_PD_9 sa_cust_item_cls.COST_PD_10 sa_cust_item_cls.COST_PD_11 sa_cust_item_cls.COST_PD_12 sa_cust_item_cls.COST_PD_13 sa_cust_item_cls.COST_PD_14 sa_cust_item_cls.COST_PD_15 sa_cust_item_cls.COST_PD_16 sa_cust_item_cls.COST_PD_17 sa_cust_item_cls.COST_PD_18 sa_cust_item_cls.COST_PD_19 sa_cust_item_cls.COST_PD_20 sa_cust_item_cls.COST_PD_21 sa_cust_item_cls.COST_PD_22 sa_cust_item_cls.COST_PD_23 sa_cust_item_cls.COST_PD_24 sa_cust_item_cls.COST_PD_25 sa_cust_item_cls.COST_LAST_PD_26 sa_cust_item_cls.COST_CURR_PD_27 sa_cust_item_cls.NOT_USED_1 01 1273 company name 112 test drive 112 test drive, cityname, st 37429 cityname st 37429 555-555-5555 555-555-5355 my name my other name my e-mail my city, st SAME DLR BP NT AR H 3 2 ALL 0 0 7232.86 5506 20932.11 16319.24 566.97 7425.21 1250 0 15 N Y Y D Y Y Y 1 0 0 0 15 546 Y 0 P N Y N B DLR 2003-11-14 2011-06-30 2011-07-11 2008-07-01 1997-11-19 UPS GROUND 2 01 1273 1 my company 113 test dr my city, st my city st 24842 1273 YKP S 13 0 0 0 0 0 0 0 0 0 13 0 64 0 0 0 0 9 0 32 0 0 0 10 0 0 0 10 0 0 0 0 0 0 0 0 0 10 0 46 0 0 0 0 6 0 18 0 0 0 7 0 0 0 01 1273 company name 113 test drive 113 test drive, cityname, st 37429 cityname st 37429 555-555-5555 555-555-5355 my name my other name my e-mail my city, st SAME DLR BP NT AR H 3 2 ALL 0 0 7232.86 5506 20932.11 16319.24 566.97 7425.21 1250 0 15 N Y Y D Y Y Y 1 0 0 0 15 546 Y 0 P N Y N B DLR 2003-11-14 2011-06-30 2011-07-11 2008-07-01 1997-11-19 UPS GROUND 2 01 1273 1 my company 114 test dr my city, st my city st 24842 1273 YKT S 0 0 0 0 0 0 0 0 0 0 0 214 214 -214 0 0 0 0 0 0 0 0 0 0 0 486 0 0 0 0 0 0 0 0 0 0 0 0 141 158 -141 0 0 0 0 0 0 0 0 0 0 0 360 0 01 1273 company name 114 test drive 114 test drive, cityname, st 37429 cityname st 37429 555-555-5555 555-555-5355 my name my other name my e-mail my city, st SAME DLR BP NT AR H 3 2 ALL 0 0 7232.86 5506 20932.11 16319.24 566.97 7425.21 1250 0 15 N Y Y D Y Y Y 1 0 0 0 15 546 Y 0 P N Y N B DLR 2003-11-14 2011-06-30 2011-07-11 2008-07-01 1997-11-19 UPS GROUND 2 01 1273 1 my company 115 test dr my city, st my city st 24842 1273 YKU S 0 0 0 0 0 0 0 0 0 923 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 697 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Quote Link to comment Share on other sites More sharing options...
fenway Posted July 15, 2011 Share Posted July 15, 2011 Well, the easiest way is to join this to a derived table on customer total sales for these 3 categories. Quote Link to comment Share on other sites More sharing options...
jakebur01 Posted July 15, 2011 Author Share Posted July 15, 2011 That's a good idea. If it were you, how would you go about doing that? Quote Link to comment Share on other sites More sharing options...
jakebur01 Posted July 15, 2011 Author Share Posted July 15, 2011 I need this within the next two hours. If I have to go through the trouble of building a new table. It might be just as fast for me to import the data from the original query into a table then query the data using php and insert the customers whos sales are above 500 into a secondary table. Quote Link to comment Share on other sites More sharing options...
ebmigue Posted July 16, 2011 Share Posted July 16, 2011 Your columns are: SALES_PD_1, ..., SALES_PD_9, SALES_PD_N Did you consider normalising this? Quote Link to comment Share on other sites More sharing options...
jakebur01 Posted July 16, 2011 Author Share Posted July 16, 2011 Does mysql not care for underscores too much? Quote Link to comment Share on other sites More sharing options...
fenway Posted July 16, 2011 Share Posted July 16, 2011 Does mysql not care for underscores too much? No, normalization in the sense of not having N columns for each same thing. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.