title: Summary and Total Invoice Data in SQL Query
body: <p>I need a complete query that can be dropped into a report displaying tool (like Crystal Reports or Report Writer).</p>
  
  <p>I've almost got this working, but I can't seem to get the <strong>Grand Totals</strong> row on the bottom.</p>
  
  <p>SQL Fiddle would not build this table, saying there is insufficient memory. (???) So, all I can do is paste it here.</p>
  
  <p>Here is my sample table:</p>
  
  <pre><code>CREATE TABLE [dbo].[jp2Invoice](
      [Id] [int] IDENTITY(1,1) NOT NULL,
      [InvoiceNumber] [varchar](20) NOT NULL,
      [Subtotal] [decimal](9, 4) NOT NULL,
      [Taxable] [varchar](1) NULL,
      [TaxRate] [decimal](9, 4) NULL,
      [TaxableAmount] [decimal](9, 4) NOT NULL,
      [NontaxableAmount] [decimal](9, 4) NOT NULL,
      [Tax] [decimal](9, 4) NOT NULL,
      [Total] [decimal](9, 4) NOT NULL,
      [AmountTendered] [decimal](9, 4) NOT NULL,
      [ChangeAmount] [decimal](9, 4) NOT NULL,
      [AmountDue] [decimal](9, 4) NOT NULL,
      [ChargeAmount] [decimal](9, 4) NOT NULL,
   CONSTRAINT [PK_jp2Invoice] PRIMARY KEY CLUSTERED ([Id] ASC) ON [PRIMARY]
  ) ON [PRIMARY]
  </code></pre>
  
  <p>Here is some sample data to go in it:</p>
  
  <pre><code>insert into jp2Invoice(
    InvoiceNumber, Subtotal, Taxable, TaxRate, TaxableAmount, NontaxableAmount, Tax, Total, AmountTendered, ChangeAmount, AmountDue, ChargeAmount
  ) values 
  ('ABC001',  19.9500, 'Y', 8.2500,  19.9500,   0.0000,  1.6458,  21.5958,  40.0000, 18.4041,   0.0000,   0.0000),
  ('ABC002', 558.8300, 'Y', 6.0000, 335.3000, 223.5300, 20.1180, 355.4180,   0.0000,  0.0000,   0.0000, 355.4180),
  ('ABC003', 281.4700, 'Y', 8.2500, 281.4700,   0.0000, 23.2212, 304.6912,   0.0000,  0.0000,   0.0000, 304.6913),
  ('ABC004',  95.9800, 'Y', 8.2500,  95.9800,   0.0000,  7.9183, 103.8983,   0.0000,  0.0000,   0.0000, 103.8983),
  ('ABC005',  73.1900, 'Y', 8.2500,  73.1900,   0.0000,  6.0381,  79.2281, 100.0000, 20.7718,   0.0000,   0.0000),
  ('ABC006', 215.6500, 'N', 0.0000,   0.0000, 215.6500,  0.0000, 215.6500,   0.0000,  0.0000,   0.0000, 215.6500),
  ('ABC007', 146.0200, 'Y', 8.2500, 146.0200,   0.0000, 12.0466, 158.0666,   0.0000,  0.0000,   0.0000, 158.0667);
  </code></pre>
  
  <p>With that data, I have created this query:</p>
  
  <pre><code>select
   case when ChargeAmount=0 then 'Cash' else 'Charge' end as 'TxType',
   TaxRate,
   sum(Subtotal) as 'SubTotal',
   sum(TaxableAmount) as 'TaxAmt',
   sum(AmountTendered) as 'Tendered',
   sum(ChangeAmount) as 'Change',
   sum(ChargeAmount) as 'Charged',
   sum(Total) as 'Total'
  from jp2Invoice
  group by case when ChargeAmount=0 then 'Cash' else 'Charge' end,
   TaxRate
  </code></pre>
  
  <p>That query produces the following table:</p>
  
  <pre><code>TxType TaxRate  SubTotal      TaxAmt    Tendered     Change  Charged    Total
  Charge  0.0000  215.6500      0.0000      0.0000     0.0000 215.6500    215.6500
  Charge  6.0000  558.8300    335.3000      0.0000     0.0000 355.4180    355.4180
  Cash    8.2500   93.1400     93.1400    140.0000    39.1759   0.0000    100.8239
  Charge  8.2500  523.4700    523.4700      0.0000     0.0000 566.6563    566.6561
  </code></pre>
  
  <p>First: It looks like that table is grouping first by the TaxRate, and then by the "TxType", even though the <strong>group by</strong> clause shows the "TxType" calculation coming first. How do I force the "Cash" to come first and the "Charge" to come next?</p>
  
  <p>Second: How would I write a <strong>Grand Total</strong> row at the bottom with the calculations showing the totals? I have hand calculated one in the sample table below:</p>
  
  <pre><code>TxType TaxRate  SubTotal      TaxAmt    Tendered     Change  Charged    Total
  Charge  0.0000  215.6500      0.0000      0.0000     0.0000  215.6500    215.6500
  Charge  6.0000  558.8300    335.3000      0.0000     0.0000  355.4180    355.4180
  Cash    8.2500   93.1400     93.1400    140.0000    39.1759    0.0000    100.8239
  Charge  8.2500  523.4700    523.4700      0.0000     0.0000  566.6563    566.6561
  Totals         1391.0900    951.91      140.0000    39.1759 1137.7243   1238.5480
  </code></pre>
  
  <p>There could be some typos in that because it was all hand edited.</p>
  
  <p>How would I get the data to display first by TxType and show a Totals row on the bottom?</p>