TR Forums

derFunkenstein · Fri Mar 07, 2014 5:28 pm

Before you ask, this is not my homework.

Working in SQL Server 2008 R2.

I've got all this junk in one table:

CustomerId
FeeID (e.g. paper, toner, office furniture, whatever)
Dollars

And I want to tie in other attributes: CompanyId in another table.

Let's call the tables tblCust and tblAccounts

And I've selected what I want into a temp table, it's just formatted wrong. Then I need to select a bunch of company attributes (address and phone and so on) plus one column per account, with one row per company. I hope this makes sense.

So I started out doing this:

select 
SUM(tblAccounts.fltAmount) 'Amount',
tblAccounts.intAcctCodeId 'AcctCode',
tblCust.intCompanyId 'CompanyId'
into #tempamounts
from tblAccounts
join tblCust on tblAccounts.intCompanyId = tblCust.intId
group by tblAccounts.intAcctCodeId, tblCust.intCompanyId

And that makes a temp table with the amount, the account code, and the company ID. From there I can join in other company information and it's all cool, except that I need it formatted in such a way that there are the 11 account codes in their own columns and one row per company.

I've figured out how to get totals for one person into that column (because my join resulted in it finding the first person at a company and moving on) - and that's OK for about half the companies involved since we only deal with one person, but with other companies there are anywhere from 2-5 people we deal with, so I have to sum those up.

I also accidentally figured out how to put the total sum for all companies into those columns, another amusing mistake.

The answer I'm dreading is that I'd be making like 11 temp tables, one for each account code. It's time consuming but doable and it'll probably come out correct in the end. I'm hoping there's a better answer. I'm self-taught SQL, really getting serious about it about 6 months ago. I'm kind of a n00b just looking for a shortcut. Any gerbils have suggestions?

Fri Mar 07, 2014 6:10 pm

What's the relationship between account codes, a "person", and company ID? Is the 'customer' a person or account? Not sure about that "11 account codes in their own columns" statement. Some sample data of what you want would be nice.

Stranger · Fri Mar 07, 2014 6:15 pm

it sounds like you're trying to attempt a pivot. but your description is a bit confusing. you might want to specify the table headers before and after

http://technet.microsoft.com/en-us/libr ... (v=sql.105).aspx

BobbinThreadbare · Fri Mar 07, 2014 8:36 pm

All my experience is with mysql and postgresql, but it should apply.

If I understand correctly, you want to select all the company information, including the sum of all the accounts for the company?

I would do the join in the reverse that you've done it.

Select the information from the company table, join the accounts on companyID, and select the sum of all the amounts. English doesn't translate very well into sql.

SELECT 
tblCust.companyInfo1,
tblCust.companyInfo2,
tblCust.companyInfo3,
SUM(tblAccounts.fltAmount)
FROM tblCust
JOIN tblAccounts on tblAccounts.intId =tblCust.intCompanyId
GROUP BY tblCust.intCompanyId

I think something like that would spit out what you want.

You should really name intId intCompanyId on the accounts table if that's what it is. Will save headaches later.

derFunkenstein · Fri Mar 07, 2014 11:54 pm

I wasn't sure if pivot was the right word when I wrote it, but that does seem to be what I'm trying to do. The data is just really highly normalized - even the addresses in the tblCust are just pointing to other tables - street ID, city/state/ZIP combo ID, and so on, but forget about that stuff - I've already got the majority under control. But that's why Company ID isn't part of the tblAccount, because it's already on tblCust - and when we update data, we really only want to update it in one place.

I'll try to explain the relationships of what I'm looking for better:

A Customer is a person who works for a Company, and we track what Company they work for in the tblCust table. The Account is something a customer pays for, which is tracked in this tblAccount. We're trying to track what company has bought what items without regard to which Customer paid for it.

So tblAccount has these things (and more that isn't really pertinent): Account code (the item number they bought, which points to a lookup table), the dollar amount, and the Customer ID.

tblCust has these things (plus other stuff): Customer ID (which is the subject of my join), name, address/phone/email contacts, Company ID (which points to another table I'm not really concerned with right now)

And what I want is this:

CompanyID, address/phone/email info, Acct1, Acct2, [...] Acctn

Those Acct fields are supposed to wind up as SUM(tblAccount.fltAmount) WHERE tblAccount.intAccountId = 'Acctn' GROUP BY intCompanyId. That was actually my first attempt, but that's obviously not the correct syntax, and turning it into a subquery results in getting the grand total rather than just that company's total. I've constructed most of this already, gotten the easy stuff out of the way. I really appreciate the responses, which I'll look into when I'm not dog tired. Thank you, all.

Sat Mar 08, 2014 12:57 am

derFunkenstein wrote:
I wasn't sure if pivot was the right word when I wrote it, but that does seem to be what I'm trying to do. The data is just really highly normalized - even the addresses in the tblCust are just pointing to other tables - street ID, city/state/ZIP combo ID, and so on, but forget about that stuff - I've already got the majority under control. But that's why Company ID isn't part of the tblAccount, because it's already on tblCust - and when we update data, we really only want to update it in one place.

Normalizing is fine. Restoring them usually involve relatively simple joins.

derFunkenstein wrote:
A Customer is a person who works for a Company, and we track what Company they work for in the tblCust table. The Account is something a customer pays for, which is tracked in this tblAccount. We're trying to track what company has bought what items without regard to which Customer paid for it.

So tblAccount has these things (and more that isn't really pertinent): Account code (the item number they bought, which points to a lookup table), the dollar amount, and the Customer ID.

tblCust has these things (plus other stuff): Customer ID (which is the subject of my join), name, address/phone/email contacts, Company ID (which points to another table I'm not really concerned with right now)

And what I want is this:

CompanyID, address/phone/email info, Acct1, Acct2, [...] Acctn

Those Acct fields are supposed to wind up as SUM(tblAccount.fltAmount) WHERE tblAccount.intAccountId = 'Acctn' GROUP BY intCompanyId. That was actually my first attempt, but that's obviously not the correct syntax, and turning it into a subquery results in getting the grand total rather than just that company's total. I've constructed most of this already, gotten the easy stuff out of the way.

Correct me if I misunderstood, but I think what you meant by "account" is more like "sales order" or "invoice" in common database examples. It threw me off because I thought multiple people can be buying under the same "company" account. So looks like Company and Customers are 1:many, Customers and "Accounts" is 1:many. Let's not worry about the address stuff for now, but if customers can have multiple addresses different from the Company, your desired results will not be entirely correct.

"N" can be different from each company and each customer? If that's the case the tabular result that you are looking for is not possible? A partial solution (someone may come up with better idea) may be a result set like this (I'm denormalizing a bit here)?

Company  Address  Item    Totals
--------------------------------
A        AddrA    Apple   50
A        AddrA    Orange  80
A        AddrA    Banana  40
B        AddrB    Banana  20
B        AddrB    Orange  30
C        AddrC    Apple   100
C        AddrC    Pear    60

derFunkenstein · Sat Mar 08, 2014 2:02 pm

Actually by account, I guess what I really mean is "item purchased", which you figured out. The way I have it (more or less) is similar to your table.

I have (kinda, I can get this on my own at least):

Customer   CompanyId         Item    Amount
     Bob           1        Apple    10.99
    John           1        Apple    10.00
     Bob           1       Orange    10.00
    John           1       Orange     5.00
     Bob           1       Banana    10.00
    John           1       Banana     2.50
     Bob           1         Plum    11.00

And what I need is:

CompanyId    Apple$    Orange$     Banana$    Plum$
         1    20.99      15.00       12.50    11.00

I started selecting totals to temp tables and it works.

drop table #bananas 

select 
SUM(tblAccounts.fltAmount) 'Amount', 
tblAccountCodes.strCode 'AcctCode', 
tblCustomer.intCompanyId 'CompanyId'
into #bananas 
from tblAccounts a
join tblCustomer cu on cu.intId = tblAccounts.lngCustomerId
join tblCompany c on c.intId = cu.intCompanyId
join tblAccountCodes ac on ac.intId = a.intAccountId
group by a.strCode, c.lngId

So now I have the bananas column created in a temp table. And I repeated for apples, bananas, plums, and so on. And now I can just draw out the amount where the account code is what I want in each column. I guess it's solved, I just don't like how I did it. I'm never going to give this to my boss or to a customer, I'm the only person in the company who will run this query (most likely) so how it looks isn't a big deal, but I know there's an easier way.

Also, I want to say abstracting this is hard, trying to keep my syntax and references correct. :lol:

Sat Mar 08, 2014 3:32 pm

derFunkenstein wrote:
Also, I want to say abstracting this is hard, trying to keep my syntax and references correct.

That's why it is good to have some concrete examples, even if a bit made up.

derFunkenstein · Mon Mar 10, 2014 9:19 am

Epiphany: one temp table is all I need if I select companyID, amount, and account code into it, and then join it multiple times using different aliases, with a "intAcctCode = 'xxxx' where xxxx is a particular code. Derp.

Thanks again everyone, sometimes just talking through it helps.

cphite · Mon Mar 10, 2014 10:03 am

Given the following tables:

create table tblAccounts (AccountID int, ItemID int, Amount float)
go
create table tblCustomer (AccountID int, CustomerName varchar(40))
go
create table tblProducts (ItemID int, Product varchar(40))
go

insert tblAccounts values (1, 1, 1213.04)
insert tblAccounts values (1, 2, 44.05)
insert tblAccounts values (1, 3, 133.54)
insert tblAccounts values (2, 1, 3535.04)
insert tblAccounts values (2, 2, 545.64)
insert tblAccounts values (2, 3, 1433.54)
insert tblAccounts values (3, 1, 2334.04)
insert tblAccounts values (3, 2, 3443.23)
insert tblAccounts values (3, 3, 242.56)

insert tblCustomer values (1, 'Bill')
insert tblCustomer values (2, 'Joe')
insert tblCustomer values (3, 'Tom')

insert tblProducts values (1, 'Apples')
insert tblProducts values (2, 'Bananas')
insert tblProducts values (3, 'Grapes')

Instead of using a temp table and joining to it again and again, you could do this:

;with list as 
   (
      select
         CustomerName,
         Product,
         sum(Amount) as Amount
      from
         tblAccounts a
         left outer join tblCustomer c on a.AccountID = c.AccountID
         left outer join tblProducts p on a.ItemID = p.ItemID
      group by
         CustomerName,
         Product
   )

select
   CustomerName,
   [Apples],
   [Bananas],
   [Grapes]
from
   (
      select   
         CustomerName,
         Product,
         Amount
      from
         list ) as ProductsByCustomer
      PIVOT
         ( sum(Amount) for Product in ([Apples],
                               [Bananas],
                               [Grapes])
         ) as myPivot

You would need to expand the column names (Apples, Bananas, etc) if more products are introduced; but aside from that, it is cleaner than having multiple joins.

If you prefer using multiple joins, you can still use the CTE (common table expression) instead of the temp table; a CTE is basically an "on the fly" view that is created when you run the query and dropped when the query is over - basically like this:

;with myCTE as ( select column1, column2 from mytable )

Once it's defined, you can select from "myCTE" in the query as you would any other table or view. The ; in front only needs to be there if there is code before the CTE is defined; I just always include it out of habit.

TR Forums

MSSQL - sums grouped by attributes

MSSQL - sums grouped by attributes

Re: MSSQL - sums grouped by attributes

Re: MSSQL - sums grouped by attributes

Re: MSSQL - sums grouped by attributes

Re: MSSQL - sums grouped by attributes

Re: MSSQL - sums grouped by attributes

Re: MSSQL - sums grouped by attributes

Re: MSSQL - sums grouped by attributes

Re: MSSQL - sums grouped by attributes

Re: MSSQL - sums grouped by attributes

Who is online