• Status: Solved
• Priority: Medium
• Security: Public
• Views: 21

# how to get 2 different group totals in one access query?

I have 2 tables, t1 (col CustomerName, InvoiceNo) and t2 (col InvoiceNo, Model, SN)

Data is like:
Table1                                                     Table2
CustomerName   InvoiceNo                  InvoiceNo   Model  SN
Name1                 Inv1                            Inv1            A          A-1
Name1                 Inv2                            Inv1            B          B-1
Name1                 Inv3                            Inv1            A          A-2
Name2                 Inv4                            Inv2            B          B-2
Name2                 Inv5                            Inv2            C          C-1

I want to get a total (count(SN)) for InvoiceNo and also total for CustomerName.

SELECT t1.CustomerName, t1.InvoiceNo, COUNT(t2.SN) as Qty FROM t1 INNER JOIN t2 on t1.InvoiceNo=t2.InvoiceNo
GROUP BY t1.CustomerName, t1.InvoiceNo

This only shows the total for InvoiceNo.  How can I get total for CustomerName and InvoiceNo in one query?  Or possible?

Thank you very much in advance.  HB
0
kg6lfz
• 2
• 2
1 Solution

Commented:
You can use the following query to do what you are looking for...

``````SELECT t1.CustomerName, COUNT(t2.SN) as Qty
FROM t1 INNER JOIN t2 on t1.InvoiceNo=t2.InvoiceNo
GROUP BY t1.CustomerName

Union all

SELECT t1.InvoiceNo, COUNT(t2.SN) as Qty
FROM t1 INNER JOIN t2 on t1.InvoiceNo=t2.InvoiceNo
GROUP BY t1.InvoiceNo;
``````

Saurabh...
0

Author Commented:
Thank you for your suggestions.  They query returns the result but it's kind of hard to summarize.  Is it possible to show the result like
Name1  Inv1   2
Name1  Inv2   1
Name1  Inv3   1
Name1          4
Name2  Inv4   3
Name2  Inv5   2
Name2  Inv6   1
Name2          6

I appreciate your inputs.  Thank you very much.
0

Commented:
Use the below mentioned query...

``````select t10.CustomerName,t10.invoice,t10.QTY
from
(SELECT t1.CustomerName,t1.InvoiceNo as invoice,COUNT(t2.SN) as Qty
FROM t1 INNER JOIN t2 on t1.InvoiceNo=t2.InvoiceNo
GROUP BY t1.CustomerName,t1.InvoiceNo

Union all

SELECT t1.CustomerName,'Total' as Invoice , COUNT(t2.SN) as Qty
FROM t1 INNER JOIN t2 on t1.InvoiceNo=t2.InvoiceNo
GROUP BY t1.CustomerName)t10
order by t10.customername;
``````

Saurabh...
0

Author Commented:
Great.  That was what I wanted.  Very appreciated.  Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.