Results 1 to 7 of 7
  1. #1
    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. #2
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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. #5
    Join Date
    Feb 2010
    Posts
    2

    how to count sum in two table

    hi Pootle and dav1mo

    thank you for the advice

    denny

  6. #6
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    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]
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  7. #7
    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
    .......
    She drank beer with Coke Cola
    and that's the way I like it...
    .......

Posting Permissions

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