Space after Mysql query

[travelkind]

Hello everyone.  I need to know how to add a space after each customer.  I have a field called Customer_Number and need to have a space after all results for a specific customer are returned.  Here is my code.  Thanks for your help!

 

SELECT

rawdata.Customer_Number,

rawdata.Ship_To_Name,

rawdata.Order_Month,

SUM(rawdata.Ship_Quantity),

SUM(rawdata.Sales_Extension)

FROM

rawdata

WHERE

rawdata.Division = '13' AND

rawdata.Invoice_Date BETWEEN '2009-01-01' AND '2010-03-31'

GROUP BY

rawdata.Customer_Number, Order_Month

ORDER BY

rawdata.Customer_Number ASC,

rawdata.Invoice_Date ASC

 

[fenway]

That's not mysql's job -- use php for this.

[travelkind]

Thanks for the response.  I have changed my criteria and what I am looking for now is a total for each customer.  I have done some research and it looks like I need to use a UNION ALL query and have one SELECT be the details and the other SELECT be the summary.  I am still trying to figure out how to make this work correctly.  Any ideas?  Here is my code:

 

SELECT

  *

FROM (

SELECT

rawdata.Customer_Number AS "Customer Number",

rawdata.Ship_To_Name AS "Ship to Name",

rawdata.Order_Month AS "Order Month Ended",

SUM(rawdata.Ship_Quantity) AS "Quantity Ordered",

SUM(rawdata.Sales_Extension) AS "Sales",

SUM(rawdata.GM_Ext) AS "Gross Profit"

FROM

rawdata

WHERE

rawdata.Division = '13' AND

rawdata.Invoice_Date BETWEEN '2010-01-01' AND '2010-03-31'

UNION ALL

SELECT

rawdata.Customer_Number AS "Customer Number",

rawdata.Ship_To_Name AS "Ship to Name",

rawdata.Order_Month AS "Order Month Ended",

SUM(rawdata.Ship_Quantity) AS "Quantity Ordered",

SUM(rawdata.Sales_Extension) AS "Sales",

SUM(rawdata.GM_Ext) AS "Gross Profit"

FROM

rawdata

WHERE

rawdata.Division = '13' AND

rawdata.Invoice_Date BETWEEN '2010-01-01' AND '2010-03-31'

GROUP BY

rawdata.Customer_Number, Order_Month

) AS dt

ORDER BY

"Customer Number" ASC,

"Quantity Ordered" ASC

[fenway]

What doesn't work about it???

[travelkind]

It is returning a quantity total for all customers instead by each customer.  I also need it to not post a month in the total SELECT summary.  Here is what it is returning:

 

13AH01  ACE HARDWARE  1/31/2010  13116.1  27261.26 8660.74

13AH01  ACE HARDWARE  1/31/2010  2063.1  4168.78  1191.1

13AH01  ACE HARDWARE  2/28/2010  1809.5  3833.74  1195.35

13AH01  ACE HARDWARE  3/31/2010  2100.3  4115.5  1194.51

13BK01  BAGEL KING  1/31/2010  1911.1  4147.01  1486.3

13BK01  BAGEL KING  2/28/2010  1400.1  3168.6  1154.44

13BK01  BAGEL KING  3/31/2010  1868  4081.42  1494.99

13BOB01  BEEF O' BRADYS  1/31/2010  681.4  1274.39  297.02

13BOB01  BEEF O' BRADYS  2/28/2010  632.9  1243.53  322.9

13BOB01  BEEF O' BRADYS  3/31/2010  649.7  1228.29  324.13

 

Here is what I would like for it to return:

 

                        Totals      5972.9  12118.02  3580.96

13AH01  ACE HARDWARE  1/31/2010  2063.1  4168.78  1191.1

13AH01  ACE HARDWARE  2/28/2010  1809.5  3833.74  1195.35

13AH01  ACE HARDWARE  3/31/2010  2100.3  4115.5  1194.51

             

                      Totals      5179.2  11397.03  4135.73

13BK01  BAGEL KING  1/31/2010  1911.1  4147.01  1486.3

13BK01  BAGEL KING  2/28/2010  1400.1  3168.6  1154.44

13BK01  BAGEL KING  3/31/2010  1868  4081.42  1494.99

             

                          Totals      1964  3746.21  944.05

13BOB01  BEEF O' BRADYS  1/31/2010  681.4  1274.39  297.02

13BOB01  BEEF O' BRADYS  2/28/2010  632.9  1243.53  322.9

13BOB01  BEEF O' BRADYS  3/31/2010  649.7  1228.29  324.13

 

 

[fenway]

MySQL doesn't do any formatting -- that's up to you in php code.

[travelkind]

Actually, I ended up using a create view to make it work.  Here is the code hopefully it can help someone:

 

STEP:1

==============================================================================

Create the View (v_rawdata) with the following code.

1. Here you can change Division or Invoice Date as per your needs

==============================================================================

 

 

create or replace view v_rawdata as

SELECT

'1' as ID,

Customer_Number as CUSTID,

'' as Customer_Number,

'' as Ship_To_Name,

'Total' as Invoice_Date,

round(SUM(Ship_Quantity), 2) as Ship_Quantity,

round(SUM(Sales_Extension), 2) as Sales_Extension,

round(SUM(GM_Ext), 2) as GM_Ext

FROM rawdata

where Division='13'

and Invoice_Date BETWEEN '2010-01-01' AND '2010-03-31' GROUP BY Customer_Number

union all

SELECT

'2' as ID,

Customer_Number as CUSTID,

Customer_Number,

Ship_To_Name,

Invoice_Date,

round(SUM(Ship_Quantity), 2) as Ship_Quantity,

round(SUM(Sales_Extension), 2) as  Sales_Extension,

round(SUM(GM_Ext), 2) as GM_Ext

FROM rawdata

where Division='13'

and Invoice_Date BETWEEN '2010-01-01' AND '2010-03-31' GROUP BY Customer_Number, Order_Month

union all

SELECT

'3' as ID,

Customer_Number as CUSTID,

'' as Customer_Number,

'' as Ship_To_Name,

'' as Invoice_Date,

'' as Ship_Quantity,

'' as Sales_Extension,

'' as GM_Ext

FROM rawdata

where Division='13'

and Invoice_Date BETWEEN '2010-01-01' AND '2010-03-31' GROUP BY Customer_Number;

 

STEP:2

==============================================================================

after created the above script use the below code to get output.

==============================================================================

 

SELECT Customer_Number, Ship_To_Name, Invoice_Date, Ship_Quantity, Sales_Extension, GM_Ext FROM v_rawdata order by CUSTID;