asked on

summary of customers/year

I have a basic table 4 columns

pay year, customer number, customer name, invoice number

Theres around 20'000 records.

What I want to do is write a query, to show per year how many transactions have gone through per customer number/name. So for example if there were 200 invoices for customer number 10056 then I'd like a summary table like

customer number - customer name - year - total invoices per this customer.

Any ideas how?

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mbizup

You can use an alias for the totals column like this:

SELECT [customer number], [customer name] ,[year], COUNT ([invoices]) AS [Total Invoices]
FROM YourTable
GROUP BY [year], [customer number], [customer name]

Open in new window

SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pau Lo

ASKER

The invoices go from 2007 - 2012. Is there anyway to limit it to just summarize where a customer has an invoice in EVERY year, and discard doing a summary for customers who dont have an invoice in EVERY year?

I.e. if custoemr A has at least 1 invoice in 2007, 08, 09, 10, 11, and 12 - do a group by/count

If customer B has at least 1 invoice in only 2008 and 2010 or any customers that dont have at least 1 invoice in every year, then discard them.

Pau Lo

ASKER

Or perhaps per customer, per year, count of invoices.

ie..

customer - year - invoices_count
-------------------------------------------
bsmith 2007 321
bsmith 2008 220
asmith 2007 231312
asmith 2008 31231

mbizup

SELECT [customer name] ,[year], COUNT ([invoices]) AS [Total]
FROM YourTable
GROUP BY  [customer name], [Year]

Open in new window

Like that?

Rey Obrero (Capricorn1)

try the queries posted above and make a comment after you've seen the results..

mbizup

Getting the order that you want in the grouping of the records is just a matter of switching the fields in the GROUP BY clause around.

Pau Lo

ASKER

I've run the queries but what I should have said in the first instance, was I only want it to summarise for customers who have 1 invoice in each unique year, if they dont have at least 1 invoice in each unique year, I am not interested in their summary

mbizup

I believe the way to do that is to have a table listing all of the possible years. You would have to join that with your table of invoices so that there is at least one row representing all possible years for each customer... and then base a grouping query like those posted above on that query. (It is very different from what you initially posted :-) )

Rey Obrero (Capricorn1)

;-)