Access subquery for Distinct Count and Count on same table-CodePudding

Hello I have browsed the forum for a while and am asking my first question here. I'm in a bit of a bind and was wondering if I could get some help out. I am using Access and have not found a good answer to the question on the Net yet.

I have a table called tblTransactions for transactions on Access 2013. It looks like this:

Transaction_ID	Customer_No	Prod_ID	Lıcence_ID
1	111	1	1
2	111	1	2
3	222	1	2
4	111	2	1
5	222	2	1
6	222	2	2
7	333	1	1

tblProd looks like:

Prod_ID	Prod_Name	Prod_Price
1	Prod 1	30
2	Prod 2	50

tblLicence looks like:

Lıcence_ID	Lıcence_Name	Lıcence_Price
1	Lıcence 1	80
2	Lıcence 2	100

The customer purchases the product once and may obtain multiple licenses for this product. The product is paid once, but for all licenses owned.

I want to create a summary list for transactions. I cannot print how many different prod it has and how many licenses it has in total next to the customer number.

The output I want should look like this:

Customer_No	Count_Uniq_Prods	Count_Licences	Sum_Prods_Price	Sum_Licences_Price
111	2	3	80	260
222	2	3	80	280
333	1	1	30	80

I tried different methods for the first 3 columns.

When I try with subquery, the Customer number and product count are correct, but it also removes duplicates from licenses.

SELECT C.Customer_No, T2.Count_Uniq_Prods, T2.Count_Licences" & _
        FROM" & _
            (SELECT T1.Customer_No, T1.Count_Uniq_Prods, Count(Lıcence_ID) As Count_Licences"
            FROM" & _
                (SELECT DISTINCT Customer_No, Lıcence_ID, Count(Prod_ID) As Count_Uniq_Prods
                FROM tblTransactions  GROUP BY Customer_No, Lıcence_ID ) AS T1
            GROUP BY T1.Customer_No, T1.Count_Uniq_Prods) AS T2
        INNER JOIN tblTransactions  AS C
        ON T2.Customer_No = C.Customer_No" & _
        GROUP BY C.Customer_No, T2.Count_Uniq_Prods, T2.Count_Licences;

When I try the left join operation, I can successfully get results for the product and license separately, but when I want to get it in a single table, the results are not what I want.

It's work for Customer_No, Count_Uniq_Prods, Sum_Prods_Price:

SELECT T.Customer_No,
Count(T.Prod_ID), SUM(tblProd.Prod_Price) AS Sum_Prods_Price
FROM ((SELECT DISTINCT Customer_No, Prod_ID FROM tblTransactions ) AS T
LEFT JOIN tblProd ON tblProd.Prod_ID= T.Prod_ID)
GROUP BY T.Customer_No;

It's work for Customer_No, Count_Licences, Sum_Licences_Price:

SELECT T.Customer_No,
Count(T.Lıcence_ID), SUM(tblLicence.[Lıcence_Price]) AS Sum_Licences_Price
FROM ((SELECT Customer_No, Lıcence_ID FROM tblTransactions ) AS T
LEFT JOIN tblLicence ON tblLicence.Lıcence_ID = T.Lıcence_ID)
GROUP BY T.Customer_No

But when I take one as a subquery inside the other, I cannot reach the desired result in both results.

I hope I was able to explain clearly. Thanks in advance for any help.

CodePudding user response：

This should work for you.

My approach was to take your problem and break it down into its constituent elements.

This query retrieves the products in the format that you requested.

select customer_no, count(t.prod_id) as Count_Uniq_Prods, 
       sum(p.prod_price) as Sum_Prods_Price
      from  ( 
         select customer_no, prod_id
         from tblTransactions t
         group by customer_no, prod_id
      ) t
     inner join tblProd p on
       t.prod_id = p.prod_id
     group by customer_no

This query retrieves the licenses in the format that you requested.

select customer_no, count(t. license_id) as Count_Licenses, 
  sum(l.license_price) as Sum_Licenses_Price
from tblTransactions t
inner join tblLicense l on 
  t.license_id = l.license_id
  group by customer_no

Finally, we put them together and get the following:

select distinct  p.customer_no, Count_Uniq_Prods, 
         Count_Licenses,
         Sum_Prods_Price,
         Sum_Licenses_Price         
from (
     select customer_no, count(t.prod_id) as Count_Uniq_Prods, 
       sum(p.prod_price) as Sum_Prods_Price
      from  ( 
         select customer_no, prod_id
         from tblTransactions t
         group by customer_no, prod_id
      ) t
     inner join tblProd p on
       t.prod_id = p.prod_id
     group by customer_no
     
 ) p
inner join (
    select customer_no, count(t. license_id) as Count_Licenses, 
      sum(l.license_price) as Sum_Licenses_Price
    from tblTransactions t
    inner join tblLicense l on 
      t.license_id = l.license_id
      group by customer_no
    ) l
 on p.customer_no = l.customer_no