Statistic Problem

14 Sep 2006 2:30 PM

tho

Hi all,
I'm searching for a simple sql statistic solution. I have 2 tables:

1st table:
website-visits   | department
----------------------------------
5    | A&B CD Z1
30 | A&B AD Z0
4    | B&B X1

2nd table:
customer     | department-searchstring
----------------------------------
Mr. X   | A&B
Mrs. Y | B&B

I need the following view:
customer   | website-visits
----------------------------------------
Mr. X   | 35
Mrs. Y | 4

It's simple with only one search-string (LIKE) but I don't know how I
can solve this problem.

Thanx

14 Sep 2006 3:01 PM

Jim Underwood

First, get all the individual records that match your criteria, then try to
sum them after.

This should match the customers to the departments/visits

Select table1.[website-visits]
, table1.department
, table2.customer
, table2.[department-searchstring]
from table1
inner join table2
on table1.department like table2.[department-searchstring] + '%'

if you need to find the search string anywhere in your department, rather
than at the beginning change the last line to:
on table1.department like '%' + table2.[department-searchstring] + '%'

Now, just add in your sum on website visits. We need to eliminate the
columns that we don't want, and add a group by clause at the end of the
query to summarize by those that we do want.

Select sum(table1.[website-visits])
--, table1.department
, table2.customer
--, table2.[department-searchstring]
from table1
inner join table2
on table1.department like table2.[department-searchstring] + '%'
group by table2.customer

For more examples, check out a couple of websites on SQL.

http://www.w3schools.com/sql/sql_intro.asp

http://sqlzoo.net/

Show quoteHide quote

"tho" <thorsten.br***@siemens.com> wrote in message
news:1158244252.518151.201650@m73g2000cwd.googlegroups.com...
> Hi all,
> I'm searching for a simple sql statistic solution. I have 2 tables:
>
> 1st table:
> website-visits   | department
> ----------------------------------
> 5    | A&B CD Z1
> 30 | A&B AD Z0
> 4    | B&B X1
>
> 2nd table:
> customer     | department-searchstring
> ----------------------------------
> Mr. X   | A&B
> Mrs. Y | B&B
>
> I need the following view:
> customer   | website-visits
> ----------------------------------------
> Mr. X   | 35
> Mrs. Y | 4
>
> It's simple with only one search-string (LIKE) but I don't know how I
> can solve this problem.
>
> Thanx
>

14 Sep 2006 3:42 PM

tho

Hi Jim,

thanks for your answer. I have written a smiliar solution but without
success.
I get this error:
Server: Msg 446, Level 16, State 9, Line 3
Cannot resolve collation conflict for like operation.

(MS SQL 8.0)

Any idea?

bye

Jim Underwood schrieb:

Show quoteHide quote

> First, get all the individual records that match your criteria, then try to
> sum them after.
>
> This should match the customers to the departments/visits
>
> Select table1.[website-visits]
> , table1.department
> , table2.customer
> , table2.[department-searchstring]
> from table1
> inner join table2
> on table1.department like table2.[department-searchstring] + '%'
>
> if you need to find the search string anywhere in your department, rather
> than at the beginning change the last line to:
> on table1.department like '%' + table2.[department-searchstring] + '%'
>
> Now, just add in your sum on website visits. We need to eliminate the
> columns that we don't want, and add a group by clause at the end of the
> query to summarize by those that we do want.
>
> Select sum(table1.[website-visits])
> --, table1.department
> , table2.customer
> --, table2.[department-searchstring]
> from table1
> inner join table2
> on table1.department like table2.[department-searchstring] + '%'
> group by table2.customer
>
> For more examples, check out a couple of websites on SQL.
>
> http://www.w3schools.com/sql/sql_intro.asp
>
> http://sqlzoo.net/
>
> "tho" <thorsten.br***@siemens.com> wrote in message
> news:1158244252.518151.201650@m73g2000cwd.googlegroups.com...
> > Hi all,
> > I'm searching for a simple sql statistic solution. I have 2 tables:
> >
> > 1st table:
> > website-visits   | department
> > ----------------------------------
> > 5    | A&B CD Z1
> > 30 | A&B AD Z0
> > 4    | B&B X1
> >
> > 2nd table:
> > customer     | department-searchstring
> > ----------------------------------
> > Mr. X   | A&B
> > Mrs. Y | B&B
> >
> > I need the following view:
> > customer   | website-visits
> > ----------------------------------------
> > Mr. X   | 35
> > Mrs. Y | 4
> >
> > It's simple with only one search-string (LIKE) but I don't know how I
> > can solve this problem.
> >
> > Thanx
> >

14 Sep 2006 3:46 PM

Jim Underwood

Look up collation in BOL (books on line) and check which collation you have
for each of those two columns. I am guessing they were created with
different collation settings and you need to specify one collation for your
query. I can't tell you off hand how to do that, but others here are much
more familiar with collation issues than I.

Show quoteHide quote

"tho" <thorsten.br***@siemens.com> wrote in message
news:1158248567.498658.234040@p79g2000cwp.googlegroups.com...
> Hi Jim,
>
> thanks for your answer. I have written a smiliar solution but without
> success.
> I get this error:
> Server: Msg 446, Level 16, State 9, Line 3
> Cannot resolve collation conflict for like operation.
>
> (MS SQL 8.0)
>
> Any idea?
>
>
> bye
>
>
> Jim Underwood schrieb:
>
> > First, get all the individual records that match your criteria, then try
to
> > sum them after.
> >
> > This should match the customers to the departments/visits
> >
> > Select table1.[website-visits]
> > , table1.department
> > , table2.customer
> > , table2.[department-searchstring]
> > from table1
> > inner join table2
> > on table1.department like table2.[department-searchstring] + '%'
> >
> > if you need to find the search string anywhere in your department,
rather
> > than at the beginning change the last line to:
> > on table1.department like '%' + table2.[department-searchstring] + '%'
> >
> > Now, just add in your sum on website visits. We need to eliminate the
> > columns that we don't want, and add a group by clause at the end of the
> > query to summarize by those that we do want.
> >
> > Select sum(table1.[website-visits])
> > --, table1.department
> > , table2.customer
> > --, table2.[department-searchstring]
> > from table1
> > inner join table2
> > on table1.department like table2.[department-searchstring] + '%'
> > group by table2.customer
> >
> > For more examples, check out a couple of websites on SQL.
> >
> > http://www.w3schools.com/sql/sql_intro.asp
> >
> > http://sqlzoo.net/
> >
> > "tho" <thorsten.br***@siemens.com> wrote in message
> > news:1158244252.518151.201650@m73g2000cwd.googlegroups.com...
> > > Hi all,
> > > I'm searching for a simple sql statistic solution. I have 2 tables:
> > >
> > > 1st table:
> > > website-visits   | department
> > > ----------------------------------
> > > 5    | A&B CD Z1
> > > 30 | A&B AD Z0
> > > 4    | B&B X1
> > >
> > > 2nd table:
> > > customer     | department-searchstring
> > > ----------------------------------
> > > Mr. X   | A&B
> > > Mrs. Y | B&B
> > >
> > > I need the following view:
> > > customer   | website-visits
> > > ----------------------------------------
> > > Mr. X   | 35
> > > Mrs. Y | 4
> > >
> > > It's simple with only one search-string (LIKE) but I don't know how I
> > > can solve this problem.
> > >
> > > Thanx
> > >
>