MySQL Getting two different total amounts based on criteria

[jmyrtle]

@r937

so you want this –

customer  salesperson  totals

Smith       S02         xxx.xx
Smith       S03         xxx.xx
Smith       S07         xxx.xx
Smith      NULL       xxxxx.xx   /* subtotal for Smith */

Jones       S01         xxx.xx
Jones       S04         xxx.xx
Jones       S07         xxx.xx
Jones      NULL       xxxxx.xx   /* subtotal for Jones */

NULL       NULL      xxxxxx.xx   /*   grand total   */   

whereas i though you wanted this –

 salesperson  customer  totals
                        
   S01        Jones      xxx.xx
   S01        NULL       xxx.xx   /* subtotal for S01 */
   
   S02        Smith      xxx.xx
   S02        NULL       xxx.xx   /* subtotal for S02 */

   S03        Smith      xxx.xx
   S03        NULL       xxx.xx   /* subtotal for S03 */

   S04        Jones      xxx.xx
   S04        NULL       xxx.xx   /* subtotal for S04 */

   S07        Smith      xxx.xx
   S07        Jones      xxx.xx
   S07        NULL       xxx.xx   /* subtotal for S07 */
                       
   NULL       NULL      xxxx.xx   /*   grand total   */   

can you see the difference?

I need these two columns in order: Salesperson (alphabetically) and Customer Number (smallest number first). My report already shows that. (These are the salesperson’s initials. There are more salespeople rather than just the ADC data you see here):

The other fields do not need to be in any particular order nor are there any grand totals per salesman in this report. That’s what the invoiced sales and the two total (above and below $1000 respectively) columns are.