Various aggregations in same query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

MyTable is as follows:
InvoiceNumber;EntityName;SupplierName;CategoryName;Spend
Invoice1;Entity1;Supplier1;Category1;Spend1
Invoice1;Entity1;Supplier2;Category1;Spend2
Invoice3;Entity2;Supplier1;Category1;Spend3

I would like to retrieve the following data in a query (named as
QueryEntity) without any intermediate query:
EntityName; CountOfSupplier; CountOfCategory;SumOfSpend; CountOfInvoice
Entity1;2;1;Spend1+Spend2;1
Entity2;1;1;Spend3;1
Please note that a mere Count function does not help (for instance, it would
return CountOfCategory = 2 for Entity1).

Eventually, I would like to build the same query for Supplier
(QuerySupplier) and Category (QueryCategory), as follows:
QuerySupplier: SupplierName; CountOfEntity; CountOfCategory;SumOfSpend;
CountOfInvoice
QueryCategory: CategoryName; CountOfSupplier; CountOfEntity;SumOfSpend;
CountOfInvoice

Do you think it is possible?
Thank you in advance
 
Let's make it clearer:

MyTable is as follows:
InvoiceNumber;EntityName;SupplierName;CategoryName;Spend
1;Marketing;IBM;Furniture;$150
1;Marketing;Hitachi;Furniture;$250
3;Finance;IBM;Furniture;$350

Indeed there are some duplicates in InvoiceNumber field. This happens when
an entity print a single invoice for several spends.

I would like to retrieve the following data in a query (named as
QueryEntity) without any intermediate query:
EntityName; CountOfSupplier; CountOfCategory;SumOfSpend; CountOfInvoice
Marketing;2;1;$400;1
Finance;1;1;$350;1
Please note that a mere Count function does not help (for instance, it would
return CountOfCategory = 2 for Marketing).
 
toobs said:
Hi,

MyTable is as follows:
InvoiceNumber;EntityName;SupplierName;CategoryName;Spend
Invoice1;Entity1;Supplier1;Category1;Spend1
Invoice1;Entity1;Supplier2;Category1;Spend2
Invoice3;Entity2;Supplier1;Category1;Spend3
Click to expand...

toobs,

When I read through this column list, there are duplicate column
names. How did that happen?

Note also that using repeating columns (Invoice1, Invoice3, etc.),
causes many problems that are difficult to work around. If you have
control of the database schema, strongly consider normalizing the
table (Google: Database Normalization).


Please note that a mere Count function does not help (for instance, it would
return CountOfCategory = 2 for Entity1).
Click to expand...

Yes, a "mere Count function" will not help. That is one of the
problems of using repeating columns.


Do you think it is possible?
Thank you in advance
Click to expand...

Let's find out what's going on with the duplicate column names,
first.

If you could post back with your sample data and your desired
results after the query runs, that would also help.


Sincerely,

Chris O.
 
Back
Top