How can I do this query over two tables? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

How can I do this query over two tables?

Author

Topic

bilal.bilal
Starting Member

9 Posts

Posted - 2014-03-25 : 05:25:04

have two tables, first table has columns id and price and the second table has id and desc and MNT and type.

What I want to do is to get the id from the first table and do the sum all the prices and in the second table get the same id and get the sum of the same desc and the same type after that get the result .

This explains what I want to do exactly

This is the first table

http://i.stack.imgur.com/pujXK.png

and this is the second table

http://i.stack.imgur.com/2TLB4.png

and I want to get this result

http://i.stack.imgur.com/zTiAh.png

Can somebody help me?

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-03-25 : 06:27:10

Create Table Price
(
ID tinyint not null Primary key,
Price money null,
)

Create Table MNT
(
ID tinyint not null Primary Key,
Des Tinyint null,
MNT Money null,
Type Varchar(5) null,
)

/* populate the Price table */
Insert Into Price (id, Price)
Select 1,20
Union All
Select 30,60
Union ALl
Select 31,24
GO

/* populate the MNT table */
Insert Into MNT (ID, Des, MNT, Type)
Select 1,2,10,'M'
Union All
Select 2, 2, 30, 'M'
Union All
Select 15,2,40,'F'
Union ALl
Select 16, 2, 60, 'F'
GO

/* select the Price per ID from the (first) Price table */
Select p.ID, SUM(p.Price) 'Price'
From [dbo].[Price] p
Group By p.ID,p.Price

/* this will get you your 104 sum */

Create Table Price
(
ID tinyint not null Primary key,
Price money null,
)

Create Table MNT
(
ID tinyint not null Primary Key,
Des Tinyint null,
MNT Money null,
Type Varchar(5) null,
)

/* populate the Price table */
Insert Into Price (id, Price)
Select 1,20
Union All
Select 30,60
Union ALl
Select 31,24
GO

/* populate the MNT table */
Insert Into MNT (ID, Des, MNT, Type)
Select 1,2,10,'M'
Union All
Select 2, 2, 30, 'M'
Union All
Select 15,2,40,'F'
Union ALl
Select 16, 2, 60, 'F'
GO

/* select the Price per ID from the Price table */
Select p.ID, SUM(p.Price) 'Price'
From [dbo].[Price] p
Group By p.ID,p.Price

/* Returns Total 104 */
Select SUM(p.Price) 'Price'
From [dbo].[Price] p

/* Returns Total for MNT for M and F */
Select m.Des,
TypeM = Case
When m.Type = 'M'
Then SUM(m.MNT)
End,
TypeF = Case
When m.Type = 'F'
Then SUM(m.MNT)
End
From [dbo].[MNT] m
Group By m.Des, m.Type

You would then need to join the 2 tables together to work out you Resulttat...

If you will it you can achieve it!!

bilal.bilal
Starting Member

9 Posts

Posted - 2014-03-25 : 06:44:28

Just I need the query fot select the result

I try this is but I want to selected data for each type

SELECT t1.id, t1_t2.desc, sum(t2_t2.MNT) AS MNT, t1.price, (t1.price / SUM(t2_t2.MNT)) * 100 AS result FROM table1 t1 INNER JOIN table2 t1_t2 ON t1_t2.id = t1.idINNERJOIN table2 t2_t2 ON t2_t2.desc = t1_t2.desc GROUP BY t1.id, t2_t2.desc

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-03-25 : 06:52:07

Ok but you ID fields do not match for F, so how can you join without adding a common join field. The join will work but you would need a case statement to for the ID field to point to the correct Type.

If you will it you can achieve it!!

bilal.bilal
Starting Member

9 Posts

Posted - 2014-03-25 : 06:58:06

this is th problem I don have any idea for sloving you have an idea for that ??

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-03-25 : 07:05:06

This FULL join will give you the result, Inner Join would only show you where ID matches = 1.

Select p.ID, m.Des, SUM(p.Price) as Result1
From [dbo].[Price] p
Full Join [dbo].[MNT] m
On p.ID = m.ID
Group By p.ID, m.MNT, m.Des

/* Also you can use a LEFT join */

Select p.ID, m.Des, SUM(p.Price) as Result1
From [dbo].[Price] p
LEFT Join [dbo].[MNT] m
On p.ID = m.ID
Group By p.ID, m.MNT, m.Des

If you will it you can achieve it!!

bilal.bilal
Starting Member

9 Posts

Posted - 2014-03-25 : 07:11:10

few munite to test that

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-03-25 : 07:15:35

This will return the maths for the 104*100

Select p.ID, m.Des,
TypeM = Case
When m.Type = 'M'
Then SUM(m.MNT)/104*100
End,
TypeF = Case
When m.Type = 'F'
Then SUM(m.MNT)/104*100
End
From [dbo].[MNT] m
LEFT Join [dbo].[Price] p
On m.ID = p.ID
Group By m.Des, m.Type, p.ID

RESULT

ID Des TypeM TypeF
---- ---- --------------------- ---------------------
NULL 2 NULL 96.15
NULL 2 28.84 NULL
1 2 9.61 NULL

If you will it you can achieve it!!

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-03-25 : 07:20:08

You really asked a Good one...My answer will be little complicated But this is the exact answer you are looking for.........

CREATE TABLE Practise1(ID INT,Price INT)
CREATE TABLE Practise2(ID INT,[Desc] INT,MNT INT,[Type] CHAR)
INSERT INTO Practise1 VALUES(1,20),(30,60),(31,24)
INSERT INTO Practise2 VALUES(1,2,10,'M'),(2,2,30,'M'),(15,2,40,'F'),(16,2,60,'F')

SELECT TOP 1 P2.ID,P2.[Desc],(SELECT SUM(P1.Price) FROM Practise1 AS P1) as MNT1,
(SELECT SUM(P3.MNT) FROM Practise2 As P3 WHERE P3.[Type] ='M' ) AS MNT2,(SELECT Top 1 P4.[Type] FROM Practise2 As P4 WHERE P4.[Type] LIKE 'M' ) AS [Type]
,CAST ((SELECT SUM(P3.MNT) FROM Practise2 As P3 WHERE P3.[Type] ='M') AS FLOAT)/CAST ((SELECT SUM(P1.Price) FROM Practise1 AS P1) AS FLOAT)*CAST (100 AS FLOAT) As Resultant
FROM Practise2 As P2
GROUP BY P2.ID,P2.[Desc],P2.[Type]
UNION ALL
SELECT TOP 1 P2.ID,P2.[Desc],(SELECT SUM(P1.Price) FROM Practise1 AS P1) as MNT1,
(SELECT SUM(P3.MNT) FROM Practise2 As P3 WHERE P3.[Type] ='F' ) AS MNT2,(SELECT Top 1 P4.[Type] FROM Practise2 As P4 WHERE P4.[Type] Like 'F' ) AS [Type]
,CAST ((SELECT SUM(P3.MNT) FROM Practise2 As P3 WHERE P3.[Type] ='F' ) AS FLOAT)/CAST ((SELECT SUM(P1.Price) FROM Practise1 AS P1) AS FLOAT)*CAST (100 AS FLOAT) As Resultant
FROM Practise2 As P2
GROUP BY P2.ID,P2.[Desc],P2.[Type]

It will be very easy if you understand the subquery Concept....

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

bilal.bilal
Starting Member

9 Posts

Posted - 2014-03-25 : 07:21:00

bu I need the result for each type for exemple

the type F have result and the type M have a result

bilal.bilal
Starting Member

9 Posts

Posted - 2014-03-25 : 07:22:12

MuralikrishnaVeera

very so complicated I try this

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-03-25 : 07:45:11

For your understanding i placed the sub query results in Temp varibles...It will be better if you run this queries so that you can easily get away with this..Hope you will Understand this pattren

DECLARE @Temp1 INT,@Temp2 INT,@Temp3 INT,@Temp4 VARCHAR(10),@Temp5 VARCHAR(10)
SET @Temp1 = (SELECT SUM(P1.Price) FROM Practise1 AS P1 )
SET @Temp2 = (SELECT SUM(P3.MNT) FROM Practise2 As P3 WHERE P3.[Type] ='M')
SET @Temp3 = (SELECT SUM(P3.MNT) FROM Practise2 As P3 WHERE P3.[Type] ='F')
SET @Temp4 = (SELECT Top 1 P4.[Type] FROM Practise2 As P4 WHERE P4.[Type] = 'M')
SET @Temp5 = (SELECT Top 1 P4.[Type] FROM Practise2 As P4 WHERE P4.[Type] Like 'F' )

SELECT TOP 1 P2.ID,P2.[Desc],@Temp1 as MNT1,
@Temp2 AS MNT2,@Temp4 AS [Type]
,CAST (@Temp2 AS FLOAT)/CAST (@Temp1 AS FLOAT)*CAST (100 AS FLOAT) As Resultant
FROM Practise2 As P2
GROUP BY P2.ID,P2.[Desc],P2.[Type]
UNION ALL
SELECT TOP 1 P2.ID,P2.[Desc],@Temp1 as MNT1,
@Temp3 AS MNT2,@Temp5 AS [Type]
,CAST (@Temp3 AS FLOAT)/CAST (@Temp1 AS FLOAT)*CAST (100 AS FLOAT) As Resultant
FROM Practise2 As P2
GROUP BY P2.ID,P2.[Desc],P2.[Type]

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

bilal.bilal
Starting Member

9 Posts

Posted - 2014-03-25 : 08:14:19

I have this warring

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\wamp\www\Bootstrap-Admin-Theme-master\ty.php on line 74

bilal.bilal
Starting Member

9 Posts

Posted - 2014-03-25 : 08:17:45

but I need do this with php so I do that

<?php

$SQL="SELECT TOP 1 P2.id,P2.[Outlet],(SELECT SUM(P1.Montant) FROM

transaction AS P1) as MNT1,
(SELECT SUM(P3.charg_amt) FROM chargeback As P3 WHERE P3.[Network]

='VISA' ) AS MNT2,(SELECT Top 1 P4.[Network] FROM chargeback As P4

WHERE P4.[Network] LIKE 'VISA' ) AS [Network]
,CAST ((SELECT SUM(P3.charg_amt) FROM chargeback As P3 WHERE P3.

[Network] ='MASTERCARD') AS FLOAT)/CAST ((SELECT SUM(P1.Montant) FROM

transaction AS P1) AS FLOAT)*CAST (100 AS FLOAT) As Resultant
FROM chargeback As P2
GROUP BY P2.id,P2.[Outlet],P2.[Network]
UNION ALL
SELECT TOP 1 P2.id,P2.[Outlet],(SELECT SUM(P1.Montant) FROM

transaction AS P1) as MNT1,
(SELECT SUM(P3.charg_amt) FROM chargeback As P3 WHERE P3.[Network]

='VISA' ) AS MNT2,(SELECT Top 1 P4.[Network] FROM chargeback As P4

WHERE P4.[Network] Like 'VISA' ) AS [Network]
,CAST ((SELECT SUM(P3.charg_amt) FROM chargeback As P3 WHERE P3.

[Network] ='VISA' ) AS FLOAT)/CAST ((SELECT SUM(P1.Montant) FROM

transaction AS P1) AS FLOAT)*CAST (100 AS FLOAT) As Resultant
FROM chargeback As P2
GROUP BY P2.id,P2.[Outlet],P2.[Network]
";

$result=mysql_query($SQL);

while($row = mysql_fetch_array($result))
{
?>

<td><?php echo $row['Paiement_Id'] ?></td>
<td><?php echo $row['Outlet'] ?></td>
<td><?php echo $row['MNT1'] ?></td>
<td><?php echo $row['charg_amt'] ?></td>
<td><?php echo $row['Resultant'] ?></td>
<td><?php echo $row['Network'] ?></td>

</tr>

<?php }?>

bilal.bilal
Starting Member

9 Posts

Posted - 2014-03-25 : 10:51:07

thanks for all I sloved the qeury

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources