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.

Answer 1

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!!

Answer 2

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

Answer 3

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?

Answer 4

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

Answer 5

got it.

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

Answer 6

Yes, or use my way above.

---

Many Thanks & Best Regards, Hua Min

Answer 7

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