Statistics - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Statistics

Author

Topic

neutcomp
Posting Yak Master

111 Posts

Posted - 2003-09-05 : 07:35:09

Hello,

I have:
tbl_via
id
id_booking
id_via

tbl_via_names
id
name

I want to know from the tbl_via where the people came from when the booked there trip.

Something like
Friend 10%
Banner 20%
Search engine 40%
other 10%
provencemaison.com 20%

id_via is linked to the tbl_via_names.

How can I make a query that gives me this result?

Thanxx
Bjorn

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-09-05 : 08:41:32

[code]
create table tbl_via ([id] int, id_via int)
create table tbl_via_names ([id] int, [name] varchar(50))

insert tbl_via_names (id, name)
select 1, 'Friend'
union select 2, 'Banner'
union select 3, 'Search Enging'
union select 4, 'other'
union select 5, 'provencemaison.com'

insert tbl_via
select 1,1
union select 2,1
union select 3,2
union select 4,5

select * from tbl_via
select * from tbl_via_names
select
vn.name,
(1.0 * count(v.id)) /
(select count(*) from tbl_via) * 100.0 as percentage
from
tbl_via_names vn
left join tbl_via v
on v.id_via = vn.id
group by
vn.name

drop table tbl_via
drop table tbl_via_names
[/code]

Jay White
{0}

neutcomp
Posting Yak Master

111 Posts

Posted - 2003-09-05 : 09:04:57

Thanxx,
but I allready had the tables and the information in the rows.

I think the query:
select
vn.name,
(1.0 * count(v.id)) /
(select count(*) from tbl_via) * 100.0 as percentage
from
tbl_via_names vn
left join tbl_via v
on v.id_via = vn.id
group by
vn.name

Should do the trick, I am going to work this out!

Cya
Bjorn

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-09-05 : 09:31:45

quote:
Originally posted by neutcomp

Thanxx,
but I allready had the tables and the information in the rows.

I only typed out the CREATE and INSERT because you didn't provide that for me in order to solve your problem. Joe Celko hates you. Refer to number 9. Unlike Joe, I was feeling a bit more charitable ... that is, until you complained.

Jay White
{0}

neutcomp
Posting Yak Master

111 Posts

Posted - 2003-09-05 : 09:47:54

Oke nice all thoses rules,

but for this verry small problem, I am not going to write down all unessary information.

Where do you see me complaning? I am telling you thanx for the code but I allready have this part! Do you thing I am complaining then?

If there was an artical of how to read someones posting i will mail it to you.

Thanxx for the information, I am going to read this article.

Cya
Bjorn

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-09-05 : 10:15:51

I have to agree with Jay on this one....what you call small and unnecessary makes the provision of help by strangers (us) a lot more difficult...remember we don't know the table structures or data "off by heart", we're trying to imagine everything from a 4 line description...and often that's very hard!.....and every obstacle put in our paths makes it less likely for a solution to be given to you by us.....but if that's something you're o.k. with, then fine.

re 'complaining'.....remember sometimes the written word/phrase can convey more than just a statement of fact...sometimes people read (or infer badly) the authors 'tone or intention'.....that's why smilies came into being!....to make it dead obvious what the mood of the author is...to add a visual comment to the written word!

neutcomp
Posting Yak Master

111 Posts

Posted - 2003-09-05 : 10:51:36

Oke I am here to learn something,

how can you make a good post with the example I used above?

---Start---

The database structure:
tbl_via
id_via (int)
id_booking (int)
id_viaNames (int)

tbl_viaNames
id_viaNames (int)
names (string)

Relation:
tbl_via.id_viaNames = tbl_viaNames.id_viaNames

Population: (Is it called this way?)
tbl_via


id_via	id_booking	id_viaNames
6	6	4
7	7	2
8	8	3
9	9	3
10	10	3


tbl_viaNames
id_viaNames	names
1	Newspaper
2	TV
3	Internet
4	Cotedazurmaison.com
5	Friends

Problem:
I want to print out for example:
names, Percentage

Newspaper 10%
TV 20%
Internet 40%
Cotedazurmaison.com 20%
Friends 10%
This is not the correct percentage!!!

Calculaton for Friends:
(Amount of Friend / Amount of total id_bookings) * 100 = .. %
etc
---END---

Hope This is a bether way? Because this cost me about 15 till 20 minuts to type.

Cya
Bjorn

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-05 : 11:16:30

quote:

Hope This is a bether way? Because this cost me about 15 till 20 minuts to type.

You should be cutting and pasting....

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-09-05 : 11:24:32

No. That is not better at all. Since you asked, you should have posted this ....
--------------------------------------------------------------------------------------
I have the following schema:


create table tbl_via_names (
    id_viaNames int not null primary key,
    names varchar(50))

create table tbl_via (
    id_via int,
    id_booking int,
    id_viaNames int references tbl_via_names(id_viaNames))

I have the following sample data:


insert into tbl_via_names
select 1, 'Newspaper'
union select 2, 'TV'
union select 3, 'Internet'
union select 4, 'Cotedazurmaison.com'
union select 5, 'Friends'

insert into tbl_via
select 6,6,4
union select 7,7,2
union select 8,8,3
union select 9,9,3
union select 10,10,3

I would like to generate the following rowset:


names                percentage  
-------------------- ----------- 
Cotedazurmaison.com  20
Friends              0
Internet             60
Newspaper            0
TV                   20

Such that the percentage column tells me the percentage of rows in tbl_via associated with each name in tbl_via_names.
--------------------------------------------------------------------------------------
The difference here is that a reader can cut and paste the DDL and DML to populate the tables and then focus on writing the query. Additionally, a well formed expected rowset allows the reader to verify and test her query to make sure it matches your requirements. When you don't post your DDL or DML you are saying, "I'm too lazy to provide you what you need to write my query for me and your time is so worthless that I'm sure you have nothing better to do than type CREATE TABLE and INSERT statements." Additionally, by providing the exact code, you prevent the reader from wasting her time as a result of a bad assumption or read of your cryptic psuedo-code.

It's simple really. Before posting, ask yourself, "would I want to spend my valuable time working on this problem for someone I don't know, never met, will likely never meet and who will likely do nothing for me in return?" I'm here posting and helping because I like to do it. But when the question is fuzzy and the information is missing, the costs of my time quickly outweight the benefits of the satisfaction I get by providing help to a stranger. And then when the poster tells me, "Gee thanks, but I've already got the CREATE TABLE part", I can't help but have mean thoughts.

Thanks for asking, though. Your effort to improve your 'netiquette', rather than piss all over my remarks, is commendable.

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources