none
SUM multiple columns across multiple tables grouping on date

    Question

  • I have two tables that have the same columns, one is the live environment and one is the archive. Then there is the companies table that hold the company name for each invoice.

    invoice head(live)

    invoice_id,completed,invoicetotals, company_id

    123,2013-01-12, 5000, 2

    222,2010-01-12, 123, 5

    archive head(archive)

    invoice_id, completed,invoicetotals, company_id

    2123, 2010-01-08, 440, 6

    9878, 2009-02-12,10000, 2

    companies

    company_id, companyname, country

    1, acme, Australia

    2, peters co, Malayasia

    3, fudge ltd, Hong Kong

    4, yellow inc, China

    5, franks garage, Singapore

    6, harry's eatery, Hong Kong

    I am trying to sum the total number of invoices, total number of invoice for each company from the live and the archive and the percentage of invoices each company has from the total invoice from all countries for 2010. Eventually I going to put this in a variable so users can select what year they want to look at.

    The T-SQL I have written nearly works. But there is a company in the live that doesn't have any invoices in the archive. So for some reason it's invoices are not being added to the total invoices. So this effects the percentages.

    select
    		country,
    		companyname,
    		sum([Number of Invoices])as 'Number of Invoices',
    		sum([Total Invoices]) as 'Total Invoices',
    		[Percentage of Total Invoices] as 'Percentage of Total Invoices'
    		
    
    from
    (
    select
    		country,
    		companyname,
    		count(distinct invoice_id) as [Number of Invoices],
    		[Total Invoices] = (select count(distinct invoice_id) from invoice_head where year(completed) = '2010' and company_id not in ('27')),
    
    		
    		[Percentage of Total Invoices] = convert(varchar,convert(MONEY,100.0 * count(distinct invoice_id)
    		/(select count(distinct invoice_id) from invoice_head where year(completed) = '2010' and company_id not in ('27')),1))+ '%'
    	
    		
    		
    
    from invoice_head h
    inner join companies c
    on h.company_id = c.company_id
    where year(completed) = '2010'
    group by country, companyname
    
    
    UNION ALL
    
    select
    		country,
    		companyname,
    		count(distinct invoice_id)as [Number of Invoices],
    		[Total Invoices] = (select count(distinct invoice_id) from archive_head where year(completed) = '2010' and company_id not in ('27')),
    
    		
    		[Percentage of Total Invoices] = convert(varchar,convert(MONEY,100.0 * count(distinct invoice_id)
    		/(select count(distinct invoice_id) from archive_head where year(completed) = '2010' and company_id not in ('27')),1))+ '%'
    	
    		
    		
    
    from archive_head h
    inner join companies c
    on h.company_id = c.company_id
    where year(completed) = '2010'
    group by country, companyname
    
    )as t1
    group by country, companyname,[Percentage of Total Invoices]
    order by country, companyname


    Results

    country companyname Number of Invoices Total Invoices Percentage of Total Invoices
    Australia      ABCD                  1212             40974                    2.96%
    Australia      DDDD                   15                      753                   1.99%

    Australia      FFFFF                  738               753                   98.01%

    Australia       LLLL                    3330             40974                    8.13%
    Australia      SSSS                 4794            40974                   11.70%
    Malaysia      VVVV                   410            40974                    1.00%
    Singapore    ASAS                   19                    40974                    0.05%

    So you can see for the australian companies 'DDDD' and 'FFFF' are grouped by themselves and are not being add to the totals of the others. These are from the first select invoice_head statement. The UNION ALL is not combing the data I was expecting.

    Can anyone assist, thanks.


    • Edited by Cameronh Tuesday, February 19, 2013 12:13 AM formating
    Tuesday, February 19, 2013 12:12 AM

Answers

  • This is good as it shows totals by country, but still need the total number of invoices for all countries?

    Try

    select
            country,
            companyname,
            [Number of Invoices],
            sum([Number of Invoices]) OVER (PARTITION BY country) as [Country Total Invoices],
            (select sum([Number of Invoices]) from t1) as [All Country Total Invoices]
            

    from
    (
    select
            country,
            companyname,
            count(distinct invoice_id)as [Number of Invoices]

    from invoice_head h
    inner join companies c
    on h.company_id = c.company_id
    where year(completed) = '2010'and c.company_id <> '27'
    group by country, companyname

    UNION ALL

    select
            country,
            companyname,
            count(distinct invoice_id)as [Number of Invoices]

    from archive_head h
    inner join companies c
    on h.company_id = c.company_id
    where year(completed) = '2010'and c.company_id <> '27'
    group by country, companyname
    )t1
    group by country,companyname, [number of invoices]


    Many Thanks & Best Regards, Hua Min

    • Marked as answer by Cameronh Tuesday, February 19, 2013 4:13 AM
    Tuesday, February 19, 2013 2:56 AM

All replies

  • declare @s table(id int,completed date,invoicetotals int,company_id int)
    insert into @s
    values (123,'01/12/2013',5000,2),
    (222,'01/12/2010',123,5)
    --select * from @s
    declare @p table(id int,completed date,invoicetotals int,company_id int)
    insert into @p
    values (2123,'01/08/2010',440,6),
    (9878,'02/12/2009',10000,2)
    --select * from @p
    declare @coun table (company_ID int,NAme varchar(20),Country varchar(20))
    Insert into @coun
    values(1, 'acme', 'Australia'),
    (2,'peters co', 'Malayasia'),
    (5, 'fudge ltd', 'Hong Kong'),
    (6, 'fudge ltd', 'USA')
    --Select * from @coun 
    declare @a int
    Select @a=COUNT(id)
     from @s where YEAR(completed)=2010
     declare @b int
    Select @b=COUNT(id)
     from @P where YEAR(completed)=2010
    select @a TotalSales,COUNT(id)as [Countrysales],(Count(id)/@a*100) as [percentagesalesofcountry],B.Country from @s A INNER JOIN @coun B on A.company_ID=B.company_ID
    where YEAR(Completed)= 2010 group by B.Country
     Union all 
    select @b TotalSales,COUNT(id)as [Countrysales],(Count(id)/@b*100) as [percentagesalesofcountry],B.Country from @P A INNER JOIN @coun B on A.company_ID=B.company_ID
    where YEAR(Completed)= 2010 group by B.Country
    


    Hope it Helps!!

    Tuesday, February 19, 2013 12:46 AM
  • Hi Cameronh,

    You subquery like

    [Total Invoices] = (select count(distinct invoice_id) from archive_head ...

    is confusing. I suggest you better join the tables directly and not to embed the conditions for some columns.


    Many Thanks & Best Regards, Hua Min

    Tuesday, February 19, 2013 1:36 AM
  • I have removed the sub queries.

    select
    		country,
    		companyname,
    		[Number of Invoices],
    		sum([Number of Invoices]) OVER (PARTITION BY country) as [Country Total Invoices]
    		
    
    from
    (
    select
    		country,
    		companyname,
    		count(distinct invoice_id)as [Number of Invoices]
    
    from invoice_head h
    inner join companies c
    on h.company_id = c.company_id
    where year(completed) = '2010'and c.company_id <> '27'
    group by country, companyname
    
    UNION ALL
    
    select
    		country,
    		companyname,
    		count(distinct invoice_id)as [Number of Invoices]
    
    from archive_head h
    inner join companies c
    on h.company_id = c.company_id
    where year(completed) = '2010'and c.company_id <> '27'
    group by country, companyname
    )t1
    group by country,companyname, [number of invoices]

    This returns.

    country companyname    Number of Invoices Country Total Invoices
    Malaysia aaaaa                         14                       1372
    Malaysia sssssss                     269                       1372
    Malaysia aaaaxxxx                    133                       1372
    Malaysia aqaqaqa                     1                       1372
    Malaysia wwwww                      32                       1372
    Malaysia sasasas                     513                       1372

    This is good as it shows totals by country, but still need the total number of invoices for all countries?

    Tuesday, February 19, 2013 2:49 AM
  • This is good as it shows totals by country, but still need the total number of invoices for all countries?

    Try

    select
            country,
            companyname,
            [Number of Invoices],
            sum([Number of Invoices]) OVER (PARTITION BY country) as [Country Total Invoices],
            (select sum([Number of Invoices]) from t1) as [All Country Total Invoices]
            

    from
    (
    select
            country,
            companyname,
            count(distinct invoice_id)as [Number of Invoices]

    from invoice_head h
    inner join companies c
    on h.company_id = c.company_id
    where year(completed) = '2010'and c.company_id <> '27'
    group by country, companyname

    UNION ALL

    select
            country,
            companyname,
            count(distinct invoice_id)as [Number of Invoices]

    from archive_head h
    inner join companies c
    on h.company_id = c.company_id
    where year(completed) = '2010'and c.company_id <> '27'
    group by country, companyname
    )t1
    group by country,companyname, [number of invoices]


    Many Thanks & Best Regards, Hua Min

    • Marked as answer by Cameronh Tuesday, February 19, 2013 4:13 AM
    Tuesday, February 19, 2013 2:56 AM
  • got it.

    sum([Number of Invoices]) OVER () as [Total Invoices]

    Tuesday, February 19, 2013 2:57 AM
  • Yes, or use my way above.

    Many Thanks & Best Regards, Hua Min

    Tuesday, February 19, 2013 3:08 AM
  • Ended up with.

    select
    		[Year],
    		country,
    		companyname,
    		[Number of Invoices],
    		sum([Number of Invoices]) OVER (PARTITION BY country) as [Country Total Invoices],
    		convert(varchar,convert(money,100.0* sum([number of invoices]) OVER (Partition by [number of invoices])/sum([Number of Invoices]) OVER (PARTITION BY country)),1) + '%'  as [Country Percentage],
    			
    		sum([Number of Invoices]) OVER () as [Total Invoices],
    		convert(varchar,convert(money,100.0* sum([number of invoices]) OVER (Partition by [number of invoices])/sum([Number of Invoices]) OVER ()),1) + '%'  as [Total Percentage]
    from
    (
    select
    		year(completed)as [Year],
    		country,
    		companyname,
    		count(distinct invoice_id)as [Number of Invoices]
    	
    from invoice_head h
    inner join companies c
    on h.company_id = c.company_id
    where year(completed) = '2010'and c.company_id <> '27'
    group by year(completed),country, companyname
    UNION ALL
    select
    		year(completed)as [Year],
    		country,
    		companyname,
    		count(distinct invoice_id)as [Number of Invoices]
    		
    from archive_head h
    inner join companies c
    on h.company_id = c.company_id
    where year(completed) = '2010'and c.company_id <> '27'
    group by year(completed),country, companyname
    )t1
    group by year,country,companyname, [number of invoices]
    order by year,country,companyname

    Tuesday, February 19, 2013 4:12 AM