Issue with Sum of field from two table
-
Hello friend!
I am facing the problem in the query. I have sql server 2000 on windows xp plateform.
I have two tables suppose table1 which have two columns (Prod, Year1Sale) and it contain data e.g.
TABLE1
Prod Year1sale
books 5
pencil 10
Rubber 20
while the other table which have the same columns named table2 contains the data e.g.
TABLE2
Prod Year1sale
books 5
pencil 10
Sharpner 5
I want to create a view which shows the result as below:
VEIW1
Prod Year1sale
books 10
pencil 20
Rubber 20
Sharpner 5
I try my best but not succeded to get the above result. Please tell me how to fix that one.
(BASIT)
-
From your sample data, is it possible that a product can appear in only one table, or do they always appear in both tables?
Without having a crystal ball in front of me, here's my guess:
create table table1
(
prod varchar(10) primary key
, sale int
)
create table table2
(
prod varchar(10) primary key
, sale int
)
insert into table1 values('books',5)
insert into table2 values('books',10)
insert into table1 values('pencil',6)
insert into table2 values('pencil',16)
insert into table2 values('Sharpener',99)
select
isnull(t1.prod,t2.prod), sum(isnull(t1.sale,0)+isnull(t2.sale,0))
from
table1 t1
full outer join
table2 t2
on
t1.prod=t2.prod
group by
t1.prod, t2.prod
drop table table1, table2
---------- -----------
books 15
pencil 22
Sharpener 99
(3 row(s) affected)
If products always appear in both tables, you can change this to an INNER JOIN.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hey Frank, why did you change your avatar??
-
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=61&messageid=173285
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Dear Frank Kalis!
Thank you for your kind help in detail. I already created all the tables just problem in query. As you ask about the product it is stated that the product may be appeared in both or in any one of the table. I try it and it works fine.
But now I have a problem how to handle it when there are three tables. The sample data and the structure of the table for your kind help is given below
The Structure of the tables are as follows:
Company1
Idint primary key
Prodvarchar
Saleint
Company2
Idint primary key
Prodvarchar
Saleint
Company3
Idint primary key
Prodvarchar
Saleint
The Sample data is as follows:
Compny1
IDProd sale
1books 5
2pencil 10
3Rubber 20
Compny2
IDProd sale
2pencil 10
3Rubber 20
Compny3
IDProd sale
1books 10
3Rubber 20
The result which is required is as follow:
ID Prod Totalsale
1books 15
2pencil 20
3Rubber 60
I need your help how to get the result with query.
Thanks.
(BASIT)
-
I see...
Is this better?
set nocount on
create table company1
(
prodID int primary key
, prod varchar(10)
, sale int
)
create table company2
(
prodID int primary key
, prod varchar(10)
, sale int
)
create table company3
(
prodID int primary key
, prod varchar(10)
, sale int
)
insert into company1 values(1,'books',5)
insert into company1 values(2,'pencil',10)
insert into company1 values(3,'rubber',20)
insert into company2 values(2,'pencil',10)
insert into company2 values(3,'rubber',20)
insert into company3 values(1,'books',10)
insert into company3 values(3,'rubber',20)
create table #tempresults
(
prodID int
, prod varchar(10)
, sale int
)
insert into #tempresults select * from company1
insert into #tempresults select * from company2
insert into #tempresults select * from company3
set nocount off
select
min(prodID) as prodID
, min(prod) as product
, sum(sale) as totalsale
from
#tempresults
group by
prodID
drop table company1, company2, company3, #tempresults
prodID product totalsale
----------- ---------- -----------
1 books 15
2 pencil 20
3 rubber 60
(3 row(s) affected)
Is there a severe reason why you have splitted this into separate tables by company? It would be better to have a single table with a column for company.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Dear Frank Kalis thank for the prompt reply. And you right that I must add a column (Company) in the table.
Tell Frank what 'set nocount on' statement will do and also tell me Will I insert data from a table into the existing table. Lets suppose
One table is which I have and it contained data
TABLE1
ID Product Sale
1 Book 10
2 pencil 5
in this ID is the primary key
TABLE2
ID Product Sale
1 Book 10
3 Rubber 5
Tell me how to insert the record of table2 in table1
Thanks
-
SET NOCOUNT ON/OFF is explained in SQL Server's online help (BOL).
I actually misplaced it in my example. It should almost always be among the first and last statement in a batch.
As for your second question, you can use INSERT INTO...SELECT which is also explained in BOL. However, given your existing table structure this will conflict with your present PRIMARY KEY CONSTRAINT. You need to identify some other key in your modified table structure. Maybe companyID, prodID...
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Dear Frank Kalis
I did as per your advised. Insert all the records in the temp table and then create a view which gives me the desired result.
One more thing what does mean the term (BOL)you used and also told me that Batch file means the script file or something else.
Thanks for your kind help.
-
BOL is the short form of Books Online and is commonly used when one refers to SQL Server's online help. Unlike other product manuals, BOL is always the first place to look for answers. You can download the lastest version for free at http://www.microsoft.com/downloads/details.aspx?FamilyID=a6f79cb1-a420-445f-8a4b-bd77a7da194b&displaylang=en.
And yes, a batch is basically nothing more than a set of statements.
Looking at your name, what is your home country?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks Frank for your help in solving the problem. My home town is Pakistan.
Regards.
(BASIT)
-
I am facing the same problem with one difference. I am storing a weeks worth of hour values in one table and the current weeks values in a different one. I need to generate a total for the last two weeks by employee number. The above example works great until I add the "where" clause on the dateid field. When I do this it will only bring back the values where the employees appear in both tables.
SELECT
ISNULL(StorageTable.EmployeeID, CurrentTable.EmployeeID) AS Expr1, SUM(ISNULL(StorageTable.RegHours, 0) + ISNULL(CurrentTable.RegHours, 0)) AS RegTot
FROM
dbo.tblDailyPay CurrentTable
FULL OUTER JOIN
dbo.tblDailyPayStorage StorageTable ON CurrentTable.EmployeeID = StorageTable.EmployeeID
GROUP BY
CurrentTable.EmployeeID, StorageTable.EmployeeID, CurrentTable.WeekYearID
Where
(CurrentTable.WeekYearID = N'200523')
ORDER BY
ISNULL(CurrentTable.EmployeeID, StorageTable.EmployeeID)
Thanks for any ideas
-
Hello Mark Harvey!
Thanks for the query. There are some question for solving your query.
1. Please tell the name and version of the database you are using.
2. please, tell the name and version of the plateform mean operating system.
3. please send the structure of tables and the sample data.
Regards.
(BASIT)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply