Various aggregations in same query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

toobs - 02 Dec 2005 11:31 GMT

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

Reply to this Message

Chris2 - 02 Dec 2005 14:00 GMT

> Hi,
>
[quoted text clipped - 3 lines]
> Invoice1;Entity1;Supplier2;Category1;Spend2
> Invoice3;Entity2;Supplier1;Category1;Spend3

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).

<snip>

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

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

<snip>

> Do you think it is possible?
> Thank you in advance

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.

Reply to this Message

toobs - 02 Dec 2005 14:20 GMT

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).

> > Hi,
> >
[quoted text clipped - 37 lines]
>
> Chris O.

Reply to this Message