Sum multiple columns and group

[Derevon]

Hello,

I have a table that looks something like this in structure:

P_date (date)
Location_No (integer)
Invoice_type_A (integer)
Invoice_type_B (integer)
Invoice_type_C (integer)
Invoice_type_D (integer)

I want to create a query which shows the sum of the number of all four invoice types for a specific date regardless of the Location_No. By putting something like "Invoices_total: Invoice_type_A + Invoice_type_B + Invoice_type_C + Invoice_type_D" in the top field I am able to get the query to show me the total number of invoices per date AND location, but I would like the total number of invoices for a given date regardless of location number. How can this be done?

Thank you,

Per

 

[pr2-eugin]

Remove the Location_No from the Totals Query you should be good to go.

 

[Derevon]

Thanks, but it seems that doesn't help (unless I misunderstood you).

I still see multiple lines for each date listed by the query even having omitted the location number field altogether. In the list that shows up I want to see each date only once and the total sum of the count of each type of invoices regardless of location number.

When I translate my query to SQL it looks like:

SELECT TableA.P_date, TableA.InvType1, TableA.InvType2, TableA.InvType3, TableA.InvType4, [InvType1]+[InvType2]+[InvType3]+[InvType4] AS Total_Invoices
FROM TableA
GROUP BY TableA.P_date, TableA.InvType1, TableA.InvType2, TableA.InvType3, TableA.InvType4, [InvType1]+[InvType2]+[InvType3]+[InvType4];

 

[pr2-eugin]

The column Total_Invoices should say Expression, not Group By. Try..

SELECT TableA.P_date, TableA.InvType1, TableA.InvType2, TableA.InvType3, TableA.InvType4, [InvType1]+[InvType2]+[InvType3]+[InvType4] AS Total_Invoices
FROM TableA
GROUP BY TableA.P_date, TableA.InvType1, TableA.InvType2, TableA.InvType3, TableA.InvType4;