从其他相同的行中添加总计 [英] Add totals from rows that are otherwise identical

问题描述

我正在处理一个查询，该查询将简化我的寄宿狗房的发票历史记录(并比较收据).

I'm working on a query that'll simplify my invoice history (and me comparing receipts) at my boarding kennel.

我正在努力从软件制作的数据库中抓取数据，而不是手动进行.我认为我有一个不错的开端，但是我有一个问题.

Instead of doing it manually, I'm working on scraping the data out of the database that the software makes. I think I've got a pretty good start, but I've got a bit of an issue.

使用Microsoft Access，我在具有所有信息的几个表之间添加了一些关系(使用INNER JOINS)，并且我具有如下SQL语句:

Using Microsoft Access, I've added some relationships (which uses INNER JOINS) between a few tables that has all the information, and I have a SQL statement like this:

SELECT 
  Invoices.InDate, 
  Pets.PtPetName, 
  [IIQuantity]*[IIEach] AS Expr1, 
  Clients.CLLastName, 
  InvoiceItems.IIItemCodeDesc, 
  Invoices.InSeq
FROM 
  (((Clients INNER JOIN Pets ON Clients.CLSeq = Pets.PtOwnerCode) 
INNER JOIN 
  Invoices ON Clients.CLSeq = Invoices.InClientSeq) 
INNER JOIN 
  InvoiceItems ON (Invoices.InSeq = InvoiceItems.IIInvSeq) 
               AND (Pets.PtSeq = InvoiceItems.IIPetSequence)) 
INNER JOIN 
  Inventory ON InvoiceItems.IIItemCode = Inventory.InvSeq
WHERE 
  (((Invoices.InDate)>#12/31/2012#))
ORDER BY 
  Invoices.InSeq;

哪个给了我类似的东西

  DATE   | NAME | TOTAL | LNAME | INVSEQ
----------------------------------------
1/1/2013 | ODIE |   15  | SMITH | 12344
1/1/2013 | ODIE |   60  | SMITH | 12344
1/1/2013 | YODA |   10  | QWERT | 12345
1/1/2013 | YODA |   25  | QWERT | 12345
1/1/2013 | YODA |   80  | QWERT | 12345
1/1/2013 | C3PO |   10  | QWERT | 12345
1/1/2013 | C3PO |   80  | QWERT | 12345

在这种情况下，发生这种情况的原因是ODIE既要洗澡也要登机，而YODA要洗澡，要登机，而C3PO只是要洗澡和登机.

In this case, it's happening because ODIE got a bath as well boarding, and YODA got a bath, boarding, and something else, where C3PO got just a bath and boarding.

我想要的是:

  DATE   | NAME | TOTAL | LNAME | INVSEQ
----------------------------------------
1/1/2013 | ODIE |   75  | SMITH | 12344
1/1/2013 | YODA |  115  | QWERT | 12345
1/1/2013 | C3PO |   90  | QWERT | 12345

因此，基本上，如果petname和invseq在任何/所有invseqs中相同，则将总数(Expr1)加起来并排成一行.

So basically if the petname and invseq are the same in any/all invseqs, add the total amounts (Expr1) up and make it one row.

我刚刚开始弄乱SQL和Access来解决类似问题，而且我不确定从哪里开始.我可以在各个表上提供更多的信息(但是它们都很杂乱，因为狗窝软件是用来处理它们的，而不是我).

I've just start messing around with SQL and Access to get something like this worked out, and I'm not really sure where to start. I can give more a bit more info on the various tables (but they're all pretty messy because the kennel software is made to deal with them, not me).

完全有必要拥有WHERE date >，因为它大约有15,000行，而我的计算机并不喜欢这样.这样可以减少到非常有用的〜500.

It's totally necessary to have the WHERE date > because it's around 15K rows and my computer wasn't liking that. That cuts it down to a very usable ~500.

导出为ex​​cel并做一些excel魔术会更容易吗?我想得到一个很好的解决方案，特别是我认为不是很好的解决方案.

Would it be easier to export to excel and do some excel magic instead? I want to get A GOOD solution, not particularly what I thought would be a good solution.

推荐答案

听起来好像您只想使用聚合函数sum()来获取每个宠物的total值.

It sure sounds like you just want to use the aggregate function sum() to get the total values for each pet.

SELECT 
  Invoices.InDate, 
  Pets.PtPetName, 
  SUM([IIQuantity]*[IIEach]) AS Total, 
  Clients.CLLastName, 
  Invoices.InSeq
FROM 
  (((Clients INNER JOIN Pets ON Clients.CLSeq = Pets.PtOwnerCode) 
INNER JOIN 
  Invoices ON Clients.CLSeq = Invoices.InClientSeq) 
INNER JOIN 
  InvoiceItems ON (Invoices.InSeq = InvoiceItems.IIInvSeq) 
               AND (Pets.PtSeq = InvoiceItems.IIPetSequence)) 
INNER JOIN 
  Inventory ON InvoiceItems.IIItemCode = Inventory.InvSeq
WHERE (((Invoices.InDate)>#12/31/2012#))
GROUP BY Invoices.InDate, 
  Pets.PtPetName, 
  Clients.CLLastName, 
  Invoices.InSeq
ORDER BY  Invoices.InSeq;

您会注意到，一旦添加了聚合函数，就会添加一个GROUP BY子句.我删除了InvoiceItems.IIItemCodeDesc列，因为它看起来不像您期望的结果.如果此描述列包含boarding，bath等，并且该宠物具有多个不同的条目，则对于每个不同的代码描述，您将有一个单独的行.

You will notice that once the aggregate function was added a GROUP BY clause was added. I removed the inclusion of the InvoiceItems.IIItemCodeDesc column because it did not look like you had it in the desired result. If this description column includes the boarding, bath, etc and the pet has multiple distinct entries then you will have a separate row for each of these distinct code description.

这篇关于从其他相同的行中添加总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！