SQL command to get SUM across several DBs

pickarooney

I've got a set of 12 databases from 12 branches of the same business (identical structure) and I'm trying to get the sum of total spend for clients across the group. I need to somehow query all the DBs one at a time and pull out a figure from a table in each and add them together, then update the table with that total. Unique clients are identifiable by a common GUID.

Example: client xyz has spent 100 euros in shop 1, 200 in shop 2 and 300 in shop 3. These figures are in the column TOTAL of the CLIENT table.

I want to query shop 1, 2 and 3 and get the SUM of the TOTAL for client xyz and then UPDATE the TOTAL column in each with this SUM.

I've been wracking my brains for a while but have nothing. I think I may need a separate temporary database?

HellFireClub

Why can't you just run a SUM query for each DB, create a variable for each time you do this that stores the SUM value, and add the variables together in your codebehind (if you are using C#, that's how I'd go at it)?

pickarooney

I hadn't planned on writing any code but I suppose if it's straightforward enough I could learn.

cython

What DBMS are you using? That may have a bearing on the most effective way to do this, depending on the functionality available. Also, are all of the DBs in the one instance? Presumably they are at least accessible from one instance anyway. Otherwise you will need code of some level, or to make them accessible.

MagicRon

Easy with SQL Server
http://msdn.microsoft.com/en-us/library/bb964743.aspx

amen

and add the variables together in your codebehind (if you are using C#, that's how I'd go at it)?

No matter what DB you are using going out to code (C#, C, perl,php etc) to perform aggregation is a really really bad idea. It will be slow, expensive, bandwidth intensive, CPU intensive, may lock the db etc.

Tom Dunne

Can you do database link? This would be one way of doing it.

pickarooney

The databases are stored at branch level and synchronised nightly on a machine at the office on one (MS SQL server) instance where they're named Branch_1, Branch_2 etc.

Does database link work on MSSQL across DBs on the same server?

Tom Dunne

pickarooney wrote: »

Does database link work on MSSQL across DBs on the same server?

No idea, I'm an Oracle man. I would imagine it would, though.

kayos

If you have all the dbs one server its easy.

When referencing the table just fully qualify the tables

Select yada yada from branch_1.dbo.table as bt1, branch_2.dbo.table as bt2 etc etc.

That should work for you. Will check when I get into the office to be sure.

pickarooney

OK, I think I have the idea. Working on two of the bases I can get

select a.guid, a.firstName, a.lastName, a.total+b.total as totalSpend from Branch1.dbo.client a inner join Branch2.dbo b on a.guid=b.guid

to give me what seems like the correct output.

Is it possible to do an inner join on 12 tables?

I'm trying with 3 first of all

select a.guid, a.firstName, a.lastName, a.total+b.total+c.total as totalSpend from Branch1.dbo.client a inner join Branch2.dbo b on a.guid=b.guid inner join Branch3.dbo c on b.guid=c.guid

but keep running into this error.

Cannot resolve the collation conflict between "French_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

even though I've run this for each database individually:

USE Tempdb
GO
ALTER DATABASE Branch1 COLLATE Latin1_General_CI_AS
GO

edit: on the last problem, it turns out there are some columns at random in some tables which are set to French and others to Latin. joy!

HellFireClub

amen wrote: »

No matter what DB you are using going out to code (C#, C, perl,php etc) to perform aggregation is a really really bad idea. It will be slow, expensive, bandwidth intensive, CPU intensive, may lock the db etc.

If I was doing this, I'd set up an SQLDataSource for each individual SUM query, I'd then use a bit of code in my codebehind, to pass the parameter value in each case, to a variable, add up the variables, etc, actually I'd probbaly sum the SUM's in a Gridview or something and present the totals in the GridView footer (this is just how I went at a similar problem recently).

I fully accept here that you are way more advanced than I am in this area, but this would tick the box that I'd need to be ticked and I don't think there would be a huge problem with the use of resources or whatever...

EDIT: I'm sure there would be a "more efficient" way of doing it, in terms of what you might call "very best practice", but if it did what I needed it to do and ran in different browsers, I'd not be getting caught up on the finer intricacies of what was happening behind the scenes to be honest, once it wasn't difficult to use due to system performance, slowness, crashing, etc.

Beano

Maybe i'm missing some complication but this seems really straightforward. I assume you have the following :

12 DBs called branch1, branch2, branch3..branch12
The structure of each database is identical as follows

guid uniqueidentifier
firstname varchar
surname varchar
total double or money or something similar

You want the total for each client across the 12 DBs. I assume that each client has the same guid across the 12 DBs.

Assuming i have the above correct you simply need to do the following :


select guid, firstName, lastName, sum(total) as clienttotal from
(
guid, firstName, lastName, total Branch1.dbo.client
union all
guid, firstName, lastName, total Branch2.dbo.client
union all
guid, firstName, lastName, total Branch3.dbo.client
union all
guid, firstName, lastName, total Branch4.dbo.client
..
..
union all
guid, firstName, lastName, total Branch12.dbo.client
) data
group by guid, firstName, lastName

This will give you the totals for each client. You can then use this info to perform your update

jimmycrackcorm

For multi-branch retail I've always used sql replication to a master database where these types of queries are carried out

pickarooney

Beano wrote: »

Maybe i'm missing some complication but this seems really straightforward. I assume you have the following :

12 DBs called branch1, branch2, branch3..branch12
The structure of each database is identical as follows

guid uniqueidentifier
firstname varchar
surname varchar
total double or money or something similar

You want the total for each client across the 12 DBs. I assume that each client has the same guid across the 12 DBs.

Assuming i have the above correct you simply need to do the following :


select guid, firstName, lastName, sum(total) as clienttotal from
(
guid, firstName, lastName, total Branch1.dbo.client
union all
guid, firstName, lastName, total Branch2.dbo.client
union all
guid, firstName, lastName, total Branch3.dbo.client
union all
guid, firstName, lastName, total Branch4.dbo.client
..
..
union all
guid, firstName, lastName, total Branch12.dbo.client
) data
group by guid, firstName, lastName

This will give you the totals for each client. You can then use this info to perform your update

I'm trying it for the first three branches but getting a syntax error near , in the third line. I'm not really familiar with the union all or the use of the brackets.

I did get it working with the inner join command (post 11) in the end, once I'd fixed the problem with the collate settings in the columns so this is resolved, but I am interested in seeing how it works with the union command.

I may have to revise the command later to calculate the totals per DB on the fly by adding up the amount spent on each ticket for a floating period so if I could get a more wieldy way of doing it

Currently I'm doing it in two lines, by calculating the total in one command and updating a temporary field with the result of

SELECT SUM(pricepaid) FROM tillTicket WHERE typeTicket in (1,2,7,8,13,14) and id between 767000000 and 803500000 and tillTicket.idClient=Client.id) where client.Id in (SELECT DISTINCT idclient FROM tillTicket WHERE  typeTicket in (1,2,7,8,13,14) and id between 767000000 and 803500000)

I'm not sure if it's worth making a big, complicated statement where two easier ones will work OK.

Beano

select guid, firstName, lastName, sum(total) as clienttotal from
(
guid, firstName, lastName, total Branch1.dbo.client
union all
guid, firstName, lastName, total Branch2.dbo.client
union all
guid, firstName, lastName, total Branch3.dbo.client
union all
guid, firstName, lastName, total Branch4.dbo.client

) data
group by guid, firstName, lastName

will give the data for the first 3 branches. the .. was to indicate that you needed to fill in identical lines for the other branches.

Beano

pickarooney wrote: »

I'm trying it for the first three branches but getting a syntax error near , in the third line. I'm not really familiar with the union all or the use of the brackets.

I did get it working with the inner join command (post 11) in the end, once I'd fixed the problem with the collate settings in the columns so this is resolved, but I am interested in seeing how it works with the union command.

I may have to revise the command later to calculate the totals per DB on the fly by adding up the amount spent on each ticket for a floating period so if I could get a more wieldy way of doing it

Currently I'm doing it in two lines, by calculating the total in one command and updating a temporary field with the result of

SELECT SUM(pricepaid) FROM tillTicket WHERE typeTicket in (1,2,7,8,13,14) and id between 767000000 and 803500000 and tillTicket.idClient=Client.id) where client.Id in (SELECT DISTINCT idclient FROM tillTicket WHERE  typeTicket in (1,2,7,8,13,14) and id between 767000000 and 803500000)

I'm not sure if it's worth making a big, complicated statement where two easier ones will work OK.

You seem to be using a different schema here. What is the structure of the database that you are querying?

pickarooney

Beano wrote: »

select guid, firstName, lastName, sum(total) as clienttotal from
(
guid, firstName, lastName, total Branch1.dbo.client
union all
guid, firstName, lastName, total Branch2.dbo.client
union all
guid, firstName, lastName, total Branch3.dbo.client
union all
guid, firstName, lastName, total Branch4.dbo.client

) data
group by guid, firstName, lastName

will give the data for the first 3 branches. the .. was to indicate that you needed to fill in identical lines for the other branches.

I know, this command is just not recognised by SQL server (2005) as is. It's the same schema in both examples; I may have typoed one of the column names in translating them into something more meaningful.

Beano

select guid, firstName, lastName, sum(total) as clienttotal from
(
select guid, firstName, lastName, total Branch1.dbo.client
union all
select guid, firstName, lastName, total Branch2.dbo.client
union all
select guid, firstName, lastName, total Branch3.dbo.client
union all
select guid, firstName, lastName, total Branch4.dbo.client

) data
group by guid, firstName, lastName


I missed out the select keyword. perils of copy and paste. try it now.

pickarooney

Cool, got it now. it seems to be much of a muchness in terms of speed but yours is a bit more readable.

amen

For multi-branch retail I've always used sql replication to a master database where these types of queries are carried out

+1 for this.

With 12 branches and 12 DBs who looks after the backup of the DBs in the remote locations?

pickarooney

amen wrote: »

+1 for this.

With 12 branches and 12 DBs who looks after the backup of the DBs in the remote locations?

The staff back up on site nightly and backups are made to a central database over the internet.

amen

backups are made to a central database over the internet

do you mean that the data is backed up to a central db or the actual physical database file is backed up and this is then sent to the central db?

If the data is backed up to the central db can you not run your query from there?

what happens it a remote db dies during the day ?

pickarooney

The head office software transfers a copy of the databases from each branch to a database instance on the machine there so there are 12 databases on the office machine. This is where I've been running the SQL commands from the beginning.

If a branch DB dies they will either restore a backup from disk in the branch or transfer the data from the head office machine back to the office, with the loss of a few hours data. It's rare enough that a branch DB dies completely. Maybe a couple of times a year over 5,000 clients altogether.

ciaranmac

pickarooney wrote: »

OK, I think I have the idea. Working on two of the bases I can get

select a.guid, a.firstName, a.lastName, a.total+b.total as totalSpend from Branch1.dbo.client a inner join Branch2.dbo b on a.guid=b.guid

to give me what seems like the correct output.

Is it possible to do an inner join on 12 tables?

I'm trying with 3 first of all

select a.guid, a.firstName, a.lastName, a.total+b.total+c.total as totalSpend from Branch1.dbo.client a inner join Branch2.dbo b on a.guid=b.guid inner join Branch3.dbo c on b.guid=c.guid

but keep running into this error.

Cannot resolve the collation conflict between "French_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

even though I've run this for each database individually:

USE Tempdb
GO
ALTER DATABASE Branch1 COLLATE Latin1_General_CI_AS
GO

edit: on the last problem, it turns out there are some columns at random in some tables which are set to French and others to Latin. joy!

If you're stuck with the columns having different collation, you can specify the collation used for any join or comparison:

select a.guid, a.firstName, a.lastName, a.total+b.total+c.total as  totalSpend
 from Branch1.dbo.client a
 inner join Branch2.dbo b on  a.guid=b.guid COLLATE Latin1_General_CI_AS
 inner join Branch3.dbo c on b.guid=c.guid COLLATE Latin1_General_CI_AS 

pickarooney

That's handy to know, thanks.