grouping rows by customer

my source flat file has many rows per customer,
but I need to transfer it to database with only one row per customer and accumulated sales (and probably do other calculations and lookups).
I understand how to do stuff with derived columns, but how can I read source file first, calculate, group and then save to database?
As I understand, the script offers only processing row by row: Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Thanks

Vlad

won't A Flat file source and aggregation transform suffice your needs?

Rafael Salas

|||

I tried this, but I do not think it would help :-(

it is more complicated calculation, than just grouping.

I would rather do it in the script load into a Collection, loop, calculate, replace, substitute etc. and then save to database.

|||

Well you know your data...good luck with that!

Rafael Salas

|||

The Aggregate transformation does more than Group By. You don't want to do this in script. You can do SUM, AVG, MIN, MAX with the Aggregrate Transformation. If you need to then do something else combine the Derived Column Transformation with it.

http://msdn2.microsoft.com/en-US/library/ms138031.aspx

grouping by the avg

I'm trying to resolve the problem :
List the customer number, customer name, rep number, and credit balance for all customers whose credit balance is greater than the average credit balance for the customers who have the same rep. Sort by rep number. Use of 'exists' is required.
See the table behind . My query is

select customer_num, customer_name, rep_num, balance
from customer a
where exists
(select * from customer b
where balance >
(select AVG(balance) from customer c
where a.rep_num=b.rep_num)
and a.rep_num=b.rep_num)
order by rep_num;

but it lists all the customers from the table. Thank you.

CUS CUSTOMER_NAME STREET CITY ST ZIP BALANCE CREDIT_LIMIT RE
-- ------- ----- ---- -- -- ---- ---- --
148 Al's Appliance and S 2837 Fillmore FL 33336 6550 7,500 20
port Greenway

282 Brookings Direct 3827 Grove FL 33321 431.5 10,000 35
Devon

356 Ferguson's 382 Northfield FL 33146 5785 7,500 65
Wildwood

408 The Everything Shop 1828 Crystal FL 33503 5285.25 5,000 35
Raven

462 Bargains Galore 3829 Grove FL 33321 3412 10,000 65
Central

524 Kline's 838 Fillmore FL 33336 12762 15,000 20
Ridgeland

608 Johnson's Department 372 Sheldon FL 33553 2106 10,000 65
Store Oxford

687 Lee's Sport and Appl 282 Altonville FL 32543 2851 5,000 35
iance Evergreen

725 Deerfield's Four Sea 282 Sheldon FL 33553 248 7,500 35
sons Columbia

842 All Season 28 Lakeview Grove FL 33321 8221 7,500 20select customer_num, customer_name, rep_num, balance
from customer a
where a.balance > (select avg(balance) from customer where rep_num = a.rep_num);

Grouping by Page Count in CR 8.5

Datasource: Access 2000
CrystalReports: 8.5
Language: Visual Basic 6

I wonder if someone can help with this.

I am have a report that collates customer details and it is currently grouped on customer number.

These details are used to generate mail shots and it is now a requirement that the report is ordered by page count.

Is there a way that the total number of pages printed for each customer number can be used to sort the report.

So far the formulas I have tried cannot be completed because the report needs to generated first so the page numbers can be calculated.

The solution can be either in code or in the report but if in the report I need to be able to obtain the page totals for each customer number into code.

Many thanks in advance.Do you want to know how many pages does Report have for each Customer?sql

Group By, Max(date) query problem

Hello all:

I have an invoice header and detail tables and a customer table using sqlserver 2005. The Detail invoice table has price and product id. The header has the date and customerID.

I need to create a list of the most recent invoice date (and the product price) for each product for each customer. I can't use the group by because when I select both the max(date) and the price (as well as the productID and customerID which all have to be included in the group by) , I get more than one date per product per customer. I can only get the most recent date when I leave out the price and headerdetailID from the field selection.

Any help would be appreciated. Here is sample data & results.

invoiceHeadertable

invheaderID CustomerID InvoiceDate

1 40 1/1/2006

2 40 4/1/2006

3 80 3/1/2006

4 80 7/1/2006

5 80 8/12/2006

invoicedetailtable

invdetail ID invheaderID productcode price

11 1 AA 1.50

12 1 BB 1.30

13 1 CC 1.00

21 2 AA 1.40

23 2 CC 2.00

24 3 AA 2.00

25 3 CC 2.10

26 3 EE 1.10

27 4 AA 1.00

28 4 CC 2.00

29 4 EE 0.99

34 5 EE 1.55

CustomerTable

CustomerID Customername

40 johnCorp

80 maryCorp

Results

customer product most recent invoice(for this product) price

JohnCorp 40 AA 4/1/2006 1.40

JohnCorp 40 BB 1/1/2006 1.30

JohnCorp 40 CC 4/1/2006 2.00

maryCorp 80 AA 7/1/2006 1.40

maryCorp 80 CC 7 /1/2006 2.00

maryCorp 80 EE 8/12/2006 1.55

Something like this 'should' work for you. (Untested)

SELECT
dt.Customer,
dt.Product,
dt.InvoiceDate
d.Price
FROM InvoiceDetailTable d
JOIN ( SELECT
Customer,
Product,
InvoiceDate = max( InvoiceDate )
FROM InvoiceHeaderTable h
JOIN InvoiceDetailTable d
ON h.InvHeaderID = d.InvHeaderID
GROUP BY
Customer,
Product
) dt
ON ( d.Customer = dt.Customer
AND d.Product = dt.Product
AND d.InvoiceDate = dt.InvoiceDate
)

|||Crapola, didn't check your tables first. So ignore the prevoius exercise in 'egg on my face'.|||

IF there was no more than one invoice per day for a customer, you could, in the derived table (dt), include in the SELECT list:

InvHeaderID = max( InvHeaderID )

And then JOIN ON InvHeaderID instead of the three fields I indicated.

Of course, if there were two invoices for the same customer in a day, that would still not be the correct solution.

|||

Well, most of the time there would only be one invoice. But as is always the case, there can be an exception.

I had also wondered if the rank and partition function in sql server 2005 could apply, then one could just use a select query (if this is possible) to return the # 1 invoice per group but I have found no examples showing this used in a group by and where there are multiple tables involved.

Thanks

smhaig

|||

select

ct.CustomerName as Customer,

ct.CustomerID as CustomerID,

idt.ProductCode as Product,

iht.InvoiceDate as MostRecentInvoiceDate,

idt.Price as Price

from InvoiceDetailTable as idt

join InvoiceHeaderTable as iht

on idt.invheaderID = iht.invheaderID

join CustomerTable as ct

on ct.CustomerID = iht.CustomerID

where not exists

(select 1

from InvoiceDetailTable as idtx

join InvoiceHeaderTable as ihtx

on idtx.InvHeaderID = ihtx.InvHeaderID

where idtx.ProductCode = idt.ProductCode

and ihtx.CustomerID = iht.CustomerID

and ihtx.InvoiceDate > iht.InvoiceDate)

order by

ct.CustomerName,

idt.ProductCode

|||

-- Using SQL Server 2005

set nocount on
set dateformat mdy

create table invoiceHeadertable(invheaderID int,CustomerID int,InvoiceDate datetime)
insert into invoiceHeadertable(invheaderID ,CustomerID ,InvoiceDate )
select 1, 40, '1/1/2006' union all
select 2, 40, '4/1/2006' union all
select 3, 80, '3/1/2006' union all
select 4, 80, '7/1/2006' union all
select 5, 80, '8/12/2006'

create table invoicedetailtable(invdetailID int, invheaderID int, productcode char(2), price decimal(5,2))
insert into invoicedetailtable(invdetailID , invheaderID , productcode , price )
select 11, 1, 'AA', 1.50 union all
select 12, 1, 'BB', 1.30 union all
select 13, 1, 'CC', 1.00 union all
select 21, 2, 'AA', 1.40 union all
select 23, 2, 'CC', 2.00 union all
select 24, 3, 'AA', 2.00 union all
select 25, 3, 'CC', 2.10 union all
select 26, 3, 'EE', 1.10 union all
select 27, 4, 'AA', 1.00 union all
select 28, 4, 'CC', 2.00 union all
select 29, 4, 'EE', 0.99 union all
select 34, 5, 'EE', 1.55

create table CustomerTable(CustomerID int, Customername varchar(10))
insert into CustomerTable(CustomerID , Customername )
select 40, 'johnCorp' union all
select 80, 'maryCorp';

with cte(customer,product,[most recent invoice(for this product)],price,rn)
as (
select c.Customername,
d.productcode,
h.InvoiceDate,
d.price,
rank() over(partition by c.Customername,d.productcode order by h.InvoiceDate desc)
from CustomerTable c
inner join invoiceHeadertable h on h.CustomerID=c.CustomerID
inner join invoicedetailtable d on d.invheaderID=h.invheaderID
)
select customer,
product,
[most recent invoice(for this product)],
price
from CTE
where rn=1
order by customer,product

|||

I want to thank Mark and Ron for their solutions and Arnie for getting me to think about two invoices on the same day for the same product and customer (which do exist actually).

I found these solutions to be on the level of advanced lessons for me to study. I have always had trouble with group by when I needed a unique ID on a table where I was selecting a max or min or first one, etc. on another field in the same table.

I have not found any good examples other than very basic ones for rank and partition so if anyone has a good site let me know. Meanwhile I will study what I have as I now have two great ways to solve my problem.

I did not give a duplicate item with my sample data so I will see how this sql 2005 query deals with this. I seem to remember something about ties and ranking and perhaps I could also use select distinct when I select the rank = 1.

The second standard sql query (Ron's) shows me both invoices when there are 2 on same date. It may be that this is the way the data should be displayed if the prices are different, so I will need to check further on that and see if I can tweak these 2 queries to deal with that.

Thank you all again

smHaig

|||

And my thanks to Mark for demonstrating the more modern solution of the two!

Ron

GROUP BY and ORDER BY

hi Guys!
I am having a table
customer {
Name varchar(255),
step int,
details varchar(255)
}
I am trying to get the list of customer name with GROUP BY, and order
it using ORDER BY
SELECT Name FROM customer GROUP BY Name ORDER BY step
but cause of ORDER BY clause it fails with
Column name 'customer.step' is invalid in the ORDER BY clause because
it is not contained in either an aggregate function or the GROUP BY
clause.
any suggestions?Hi
You'll have to include a step column within a SELECT stratement (at least)
and then ORDER BY this column
<sharma.vasudev@.gmail.com> wrote in message
news:1133327048.081452.157790@.z14g2000cwz.googlegroups.com...
> hi Guys!
> I am having a table
> customer {
> Name varchar(255),
> step int,
> details varchar(255)
> }
> I am trying to get the list of customer name with GROUP BY, and order
> it using ORDER BY
> SELECT Name FROM customer GROUP BY Name ORDER BY step
> but cause of ORDER BY clause it fails with
> Column name 'customer.step' is invalid in the ORDER BY clause because
> it is not contained in either an aggregate function or the GROUP BY
> clause.
> any suggestions?
>|||Hi Vasudev,
There is a problem with Table design, looking at the query you are executing
.
Try the following query. Please note that I am using Step in select list
(for same error u got).
SELECT Distinct Name,Step FROM customer ORDER BY step
Vishal Khajuria
9886170165
IBM Bangalore
"sharma.vasudev@.gmail.com" wrote:

> hi Guys!
> I am having a table
> customer {
> Name varchar(255),
> step int,
> details varchar(255)
> }
> I am trying to get the list of customer name with GROUP BY, and order
> it using ORDER BY
> SELECT Name FROM customer GROUP BY Name ORDER BY step
> but cause of ORDER BY clause it fails with
> Column name 'customer.step' is invalid in the ORDER BY clause because
> it is not contained in either an aggregate function or the GROUP BY
> clause.
> any suggestions?
>|||ok, i did that with following query
SELECT Name, step from customer group by Name order by step
it came out with following error,
Server: Msg 8120, Level 16, State 1, Line 1
Column 'customer.step is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
so i also tried GROUP BY
SELECT Name, step FROM customer GROUP BY Name, step ORDER BY step
but as expected it returned the result which was of no use to me :(
any suggestions?|||hi Vishal,
i am really new with SQL stuff, kinda newbie, I am not sure what you
are pin-pointing to when you said 'There is a problem with Table
design' i would really appreciate if you could explain me a bit?
/dev|||To make things easy for everyone, can you just post some sample rows and
expected result?
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"hack_tick" <sharma.vasudev@.gmail.com> wrote in message
news:1133336153.369184.296940@.o13g2000cwo.googlegroups.com...
> ok, i did that with following query
> SELECT Name, step from customer group by Name order by step
> it came out with following error,
> Server: Msg 8120, Level 16, State 1, Line 1
> Column 'customer.step is invalid in the select list because it is not
> contained in either an aggregate function or the GROUP BY clause.
> so i also tried GROUP BY
> SELECT Name, step FROM customer GROUP BY Name, step ORDER BY step
> but as expected it returned the result which was of no use to me :(
> any suggestions?
>|||Hi Vasudev,
The point to be noticed here is that the table does not conform to rules of
normalization. You should have one more table for Name of customers.
As per your question you were trying to get list of cusotmer names by using
Group By clause --
I did not see any reason why do you need group by clause.
What I could guess is that you want distinct names of customer, that is why
I suggested query with distinct and not group by. But here also you need to
keep step in Select clause.
Please let me know if you have any more questions.
--
Vishal Khajuria
9886170165
IBM Bangalore
"hack_tick" wrote:

> hi Vishal,
> i am really new with SQL stuff, kinda newbie, I am not sure what you
> are pin-pointing to when you said 'There is a problem with Table
> design' i would really appreciate if you could explain me a bit?
> /dev
>|||hi Vishal
The Table used was just for explanation, I am having a much bigger
table with 100's of columns and many complex relationship, also the
format of table is fixed and not suppose to change :(
I shall post a complete test case with insert in a while :)|||hi guys! maybe you all can have a look at the following query!
I need to have the name of the customer displayed, but they have to be
order by column 'step'
create table customer (
name varchar(255),
step int,
details varchar(255)
)
insert into customer values('cust-1', 1, 'details-1')
insert into customer values('cust-2', 2, 'details-2')
insert into customer values('cust-2', 3, 'details-3')
insert into customer values('cust-2', 4, 'details-4')
insert into customer values('cust-3', 5, 'details-5')
insert into customer values('cust-3', 6, 'details-6')
insert into customer values('cust-3', 3, 'details-7')
insert into customer values('cust-1', 4, 'details-8')
insert into customer values('cust-4', 5, 'details-9')
insert into customer values('cust-4', 1, 'details-10')
insert into customer values('cust-5', 1, 'details-11')
insert into customer values('cust-6', 3, 'details-11')
select name from customer group by name
i have tried following queries with following error
1) SELECT Name FROM customer GROUP BY Name ORDER BY step
but cause of ORDER BY clause it fails with
Column name 'customer.step' is invalid in the ORDER BY clause because
it is not contained in either an aggregate function or the GROUP BY
clause.
2) SELECT Name, step from customer group by Name order by step
it came out with following error,
Server: Msg 8120, Level 16, State 1, Line 1
Column 'customer.step is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
so i also tried GROUP BY
SELECT Name, step FROM customer GROUP BY Name, step ORDER BY step
but as expected it returned the result which was of no use to me :(
any suggestions?
PS: Sorry for any redundant data from my earlier post, just wanted to
have a common place for your all to look at :)|||what I want is to have the List of UNIQUE Customer name, but they have
to be sorted using field 'step'