I am not good at SQL - hence here goes
Consider this scenario of 2 tables X and Y with a many to many
relationship
Table X (name,weightage )
X1 2
X2 1
X3 5
X4 1
Table Y (name ,attrib)
Y1 attrib1
Y2 attrib2
Y3 attrib3
Y4 attrib4
Relationship table Z (id,id)
X1 Y1
X1 Y2
X2 Y1
X3 Y1
X3 Y2
X3 Y4
X4 Y4
So based on Z the distribution (count of Y/ Total Y ) is like
Y1 = 100*3/7 Y2=100*2/7 Y3=100*0/7 Y4=100*2/7
Now I want to factor in the weighatge column in X also
So
X1 Y1 2*1
X1 Y2 2*1
X2 Y1 1*1
X3 Y1 5*1
X3 Y2 5*1
X3 Y4 5*1
X4 Y4 1*1
Sum: 21
Given these tables ,How would I factor in the weights and get the
distribution in a single SQL query.
Desired Output of SQL Query
Id Attrib Distrib
--------------------------------
Y1 attrib1 100*8/21
Y2 attrib2 100*7/21
Y3 attrib3 0
Y4 attrib4 100*6/21
Thx
JP
19  2051 
Oracle or SQLServer? This was tested on SQLServer 2000 but is ANSI-92 so I
think it should be OK on Oracle 9.
CREATE TABLE TableX (xcol CHAR(2) PRIMARY KEY, weightage INTEGER NOT NULL)
INSERT INTO TableX (xcol, weightage) VALUES ('X1',2)
INSERT INTO TableX (xcol, weightage) VALUES ('X2',1)
INSERT INTO TableX (xcol, weightage) VALUES ('X3',5)
INSERT INTO TableX (xcol, weightage) VALUES ('X4',1)
CREATE TABLE TableY (ycol CHAR(2) PRIMARY KEY, attrib VARCHAR(7) NOT NULL)
INSERT INTO TableY (ycol, attrib) VALUES ('Y1','attrib1' )
INSERT INTO TableY (ycol, attrib) VALUES ('Y2','attrib2' )
INSERT INTO TableY (ycol, attrib) VALUES ('Y3','attrib3' )
INSERT INTO TableY (ycol, attrib) VALUES ('Y4','attrib4' )
CREATE TABLE TableZ (xcol CHAR(2) REFERENCES TableX (xcol), ycol CHAR(2)
REFERENCES TableY (ycol), PRIMARY KEY (xcol,ycol))
INSERT INTO TableZ (xcol, ycol) VALUES ('X1','Y1')
INSERT INTO TableZ (xcol, ycol) VALUES ('X1','Y2')
INSERT INTO TableZ (xcol, ycol) VALUES ('X2','Y1')
INSERT INTO TableZ (xcol, ycol) VALUES ('X3','Y1')
INSERT INTO TableZ (xcol, ycol) VALUES ('X3','Y2')
INSERT INTO TableZ (xcol, ycol) VALUES ('X3','Y4')
INSERT INTO TableZ (xcol, ycol) VALUES ('X4','Y4')
SELECT Y.ycol, Y.attrib,
COALESCE(100*SU M(X.weightage)/
(SELECT CAST(SUM(X.weig htage) AS REAL)
FROM TableX AS X
JOIN TableZ AS Z
ON X.xcol = Z.xcol),0)
FROM TableY AS Y
LEFT JOIN TableZ AS Z
ON Y.ycol = Z.ycol
LEFT JOIN TableX AS X
ON Z.xcol = X.xcol
GROUP BY Y.ycol, Y.attrib
--
David Portas
SQL Server MVP
--
Daniel,
Re-read John's post or check out the DDL I posted. John has a joining table
for the many-to-many relationship.
--
David Portas
SQL Server MVP
--
>> All many-to-many relationships must be resolved when moving from a
logical design to a physical design. <<
Can you explain what you mean by this? What does a many-to-many relationship
between two entities, which is a logical issue, have to do with physical
design?
--
- Anith
( Please reply to newsgroups only )
Anith Sen wrote: All many-to-many relationships must be resolved when moving from a
logical design to a physical design. <<
Can you explain what you mean by this? What does a many-to-many relationship
between two entities, which is a logical issue, have to do with physical
design?
A typical many to many relationship would be ... a student can be in
many classes and a class has many students. This is true in the
logical model but should never be implemented in a relational database
using two tables: Rather using three.
STUDENT ... PK = person_id
CLASS ..... PK = class_id
STUDENT_CLASS (intersecting entity) ... PK person_id and class_id.
--
Daniel Morgan http://www.outreach.washington.edu/e...ad/oad_crs.asp http://www.outreach.washington.edu/e...oa/aoa_crs.asp da******@x.wash ington.edu
(replace 'x' with a 'u' to reply)
Representation of data in a relational database is by definition, logical. I
am more interested in knowing why this schema, irrespective of the number of
tables, has anything to do with physical design.
--
- Anith
( Please reply to newsgroups only )
Anith Sen wrote: Representation of data in a relational database is by definition, logical. I
am more interested in knowing why this schema, irrespective of the number of
tables, has anything to do with physical design.
We are talking here about Database 101. If you need a course in
relational theory and relational database architecture take it at a
local college or university. If you are not familiar with the work of
E.F. Cobb and Chris Date you have no business getting behind the keyboard.
--
Daniel Morgan http://www.outreach.washington.edu/e...ad/oad_crs.asp http://www.outreach.washington.edu/e...oa/aoa_crs.asp da******@x.wash ington.edu
(replace 'x' with a 'u' to reply)
>>If you are not familiar with the work of E.F. Cobb and Chris Date you have no business getting behind the keyboard.
If you lack social skills...you have no business getting behind the keyboard.
BZ
Daniel Morgan <da******@x.was hington.edu> wrote in message news:<107631132 1.975718@yasure >... Anith Sen wrote: Representation of data in a relational database is by definition, logical. I
am more interested in knowing why this schema, irrespective of the number of
tables, has anything to do with physical design.
We are talking here about Database 101. If you need a course in
relational theory and relational database architecture take it at a
local college or university. If you are not familiar with the work of
E.F. Cobb and Chris Date you have no business getting behind the keyboard.
xAvailx wrote: If you are not familiar with the work of
E.F. Cobb and Chris Date you have no business getting behind the keyboard.
If you lack social skills...you have no business getting behind the keyboard.
BZ
So because you are unqualified to do the job you take money for you
think it appropriate to disparage others that are qualified and point
out the minimal qualifiecation set: Fascinating.
I presume you are not a hypocrite and when you need heart surgery some
day you will not go to a board certified cardiologist but rather will go
to some guy that has a great personality.
It is your heart ... it is not your database. Do your employer a favor
and either learn what you are doing or do the ethical thing and quit.
--
Daniel Morgan http://www.outreach.washington.edu/e...ad/oad_crs.asp http://www.outreach.washington.edu/e...oa/aoa_crs.asp da******@x.wash ington.edu
(replace 'x' with a 'u' to reply) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Ali Eghtebas |
last post by:
Hi,
I've made this regex to catch the start of a valid multiline comment such as
"/*" in e.g. T-SQL code.
"(?<=^(?:*'*')*?*)(?<!^(?:*'*')*?--.*)/\*.*?$"
With Multiline option on.
As we know the T-SQL single line comment starts with a "--" and the string
character is a "'".
Considering all this, from these lines below the pattern will only catch "/*
|
by: MGFoster |
last post by:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
I've converted an ACC97 .mdb file to an ACC2K2 .adp. A report that
worked in ACC97 doesn't work in ACC2K2.
Report setup:
ACC97 ACC2K2 (SP-2)
-------------- ---------------------------
|
by: Profetas |
last post by:
Hi
I have been trying to add a mid-rule to my yacc grammar but I always get
shift and reduce conflicts
pel-0.0.3.y contains 12 shift/reduce conflicts and 12 reduce/reduce
conflicts.
does anobody know other way? I was using yacc and then I tried bison but
they are the same, I have done what is in the manual but there they have a
very limited mid-rule topic
|
by: Alvin Bruney |
last post by:
I have an array list of queries. The arraylist is variable, anywhere from 10
to 10000 or more. I'd like to spin threads to take chunks of 500 queries out
of that array list, no more than 10 threads (context switching reasons). if
it's less than 500 i spin only one thread.
I am having trouble building an efficient, clean implementation of this.
Clean means, i don't want to loop from 500 to 1000 if i only have 650 items,
i would like to...
|
by: Chumma Dede |
last post by:
Hi,
I need to code a DLL in .NET which logs the response times for our asp.net
multi-tier application. The problem is we need to log the timestamps at
multiple stages in a process lifecycle roundtrip without too much overhead.
We have two webservers which are load balanced and the back end tiers
include Biztalk and several remoting components all of which should ideally
use this same dll for logging timestamps in a central location in...
| | |
by: Aaron |
last post by:
I've got a doozie of a problem! I and others have been trying to figure this
out for too long and I've come to the conclusion that I should probably look
for some support..
Ok, I have a COM component written in C++ (I don't have the source just the
binary) and am referencing it from VB. I would say about 99% of the
functionality exposed by this COM component works fine from vb .net but I am
having a problem calling a method which has a...
|
by: comp.lang.tcl |
last post by:
The TCL command I am using will do a command-line action on a PHP
script:
set cannotRunPHP
I have to do it this way as both the TCL script and the PHP script run
as CLI. However, "info.php" requires user input to run; this causes
the TCL script calling the PHP script to hose up and die.
Is there a way I can do this so that the TCL script can call the PHP
|
by: ManningFan |
last post by:
I need to use late binding in a project because it's company standard
to not include references which aren't MS defaults, so I can't add the
scripting runtime.
I need to be able to search folders (and sub-forders) for a file name,
and then copy the file to a specified directory (at the moment it's
C:\TempCD but that will change later.
All I have so far is the following, I believe my main issue to tackle
is to figure out how to get to...
|
by: byquestion |
last post by:
Hi there xslt gurus,
i am kinda new to xslt and having difficulty to implement my some iterations. i need to recreate an xml file by using xslt. here is the sample xml file(input for xslt)
<Flights>
<Flight>
<FlightLeg >
<BookingClassCodeList>
<BookingCode Value="C" />
<BookingCode Value="Y" />
</BookingClassCodeList>
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| | |
