# Thread: how to count of sum of sum in two table

1. Registered User
Join Date
Feb 2010
Posts
2

## Unanswered: how to count of sum of sum in two table

hello all
i have proble to count addition of sum of a column's value of table1 with sum of
a column's in another table

for example in

in table1 I have name ,date.and nominal
name is for name of fruit
date is for the date I bought the fruit
nominal is for the money i spent to buy the fruit.

in table2 I have name ,date.and nominal
name is for name of drink
date is for the date I bought the drink
nominal is for the money i spent to buy the drink

then I want to count the sum of money I have spent for fruit and drink in a month

How is the statement ? is it possible in one line statement only.

thank you.
denny42

2. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Hi denny42

How come you store these two items in two tables, instead of one single table? It would be the correct design and make this problem simpler.

3. Registered User
Join Date
Dec 2007
Location
Richmond, VA
Posts
1,328
Listen to Pootle.

As for the SQL, it would look ugly, due to your table design.

Code:
```Select sum(nominal)
from (select sum(nominal) as nominal
from fruit
where name = 'orange'
union all
select sum(nominal) as nominal
from drink
where name = 'cola') as a```
Dave

4. Registered User
Join Date
Dec 2007
Location
Richmond, VA
Posts
1,328
Whereas, if you listened to pootle you could have something like:

Code:
```select sum(nominal)
from inventory
where type in ('fruit','drink')```
-- notice new column to depict what type of grocery item

or:
Code:
```select sum(nominal)
from inventory
where name in ('orange','cola')```
Dave

5. Registered User
Join Date
Feb 2010
Posts
2

## how to count sum in two table

hi Pootle and dav1mo

thank you for the advice

denny

6. Registered User
Join Date
Sep 2001
Location
Chicago, Illinois, USA
Posts
603
Haven't tested this, but this is another way to accomplish what you want. You would add whatever criteria, as you wish.

Code:
```select  Fruits.SumOfFruits+Drinks.SumOfDrinks
from    (
select  1 'Key'
,SUM(Nominal) SumOfFruits
from    dbo.tblFruits
) Fruits
inner
join    (
select  1 'Key'
,SUM(Nominal) SumOfDrinks
from    dbo.tblDrinks
) Drinks on
Drinks.[Key]=Fruits.[Key]```

7. Registered User
Join Date
Feb 2009
Posts
51

## also simply

DECLARE @FRUITS TABLE
(
NAME VARCHAR(10),

DATE DATETIME,

NOMINAL DECIMAL(8,2)
)

DECLARE @DRINKS TABLE
(
NAME VARCHAR(10),

DATE DATETIME,

NOMINAL DECIMAL(8,2)
)

INSERT INTO @FRUITS (NAME, DATE, NOMINAL)
SELECT 'APPLE', '01/Jan/2010', 10.00
UNION ALL
SELECT 'APPLE', '01/Jan/2010', 5.00
UNION ALL
SELECT 'BANANA', '02/Jan/2010', 5.00
UNION ALL
SELECT 'MANGO', '08/Jan/2010', 5.00

INSERT INTO @DRINKS (NAME, DATE, NOMINAL)
SELECT 'BEER', '01/Jan/2010', 10.00
UNION ALL
SELECT 'BEER', '02/Jan/2010', 5.00
UNION ALL
SELECT 'WINE', '02/Jan/2010', 5.00
UNION ALL
SELECT 'VODKA', '01/Jan/2010', 5.00

SELECT CASE WHEN (GROUPING( TYPE) = 1 )THEN 'FRUITS & DRINKS ' ELSE TYPE END AS 'TYPE', SUM(TOTAL) AS 'TOTAL', CONVERT(VARCHAR,MIN(DATE),106) AS DATE FROM
(
SELECT 'FRUITS' AS 'TYPE', SUM([NOMINAL]) AS 'TOTAL', MIN(DATE) AS DATE
FROM @FRUITS
WHERE DATE = '01/Jan/2010'
UNION
SELECT 'DRINKS' AS 'TYPE', SUM([NOMINAL]) AS 'TOTAL' , MIN(DATE) AS DATE
FROM @DRINKS
WHERE DATE = '01/Jan/2010'
) D GROUP BY TYPE WITH CUBE

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•