Getting distinct counts in a totals query

[John T Ingato]

Lets say I have a few tables;

table1 Customers: CustomerID, Name,Address,..
table2: Products: ProductID, Description, Cost
table3: Transactions: CustomerID, ProdID, Quantity

with the following dataset:
CustID ProdID Quantity

BOB1 1001 10
BOB1 1002 5
JOE1 1004 5
SMS 1001 10
SMS 1002 10
SMS 1004 5
BOB1 1001 10
SMS 1002 5
BOB1 1004 10

If I run a totals query based on ProdID to show performance of each item;
....
Select ProdID, Sum(Quantity) as Total From table3 Group By ProdID

I can get the following:
ProdID Total
1001 30
1002 10
1004 20

But what if I want to know number of "distinct" accounts that were part of
that calculation... e.g.
ProdID Total Accts
1001 30 2 ... 2 Bobs + 1 SMS or 2 separate
accounts
1002 10 2 ...(1 BOB) + (1 SMS) or 2 separate
accounts
1004 10 3 ...(1 BOB) + (1 SMS) + (1 JOE) or 3
separate accounts

How do I accomplish this?

I tried entering in the totals query, a field value of - Accts:
Count(Select Distinct ProdID from table 3). The failure message I get is
"At most one record can be returned from this subquery"

TRY 2:
SELECT DISTINCT table3.ProdID,
Sum(table3.Quantity AS [Total],
Count(A.StoreNumber) AS StoresReporting

FROM table3 INNER JOIN table3 AS A
ON table3.ItemNumber = A.ItemNumber
GROUP BY tblCalculatedSalesDataHomeDepot.ItemNumber;

This one jus ran forever without any result.. It seemed to hang up.

CAN SOMEONE PLEASE TELL ME HOW TO DO THIS???

 

[Jason Lepack]

I'm still trying to learn subqueries, but I came up with this.

SELECT A.ProdID, A.accnt, B.tot
FROM [SELECT ProdID, Count(CustID) AS accnt
FROM
(SELECT ProdID, CustID
FROM table3
GROUP BY ProdID, CustID) AS T
GROUP BY ProdID]. AS A INNER JOIN [SELECT ProdID, Sum(Quantity) AS
tot
FROM table3
GROUP BY ProdID]. AS B ON B.ProdID = A.ProdID;

Here are my results, I wonder that the difference in our results is an
error on your part or mine:

ProdID accnt tot
1001 2 30
1002 2 20
1004 3 20

Cheers,
Jason Lepack

Try this one

 

[John T Ingato]

Thanks Jason, I am still learning subqueries too. You code help out a lot.

I'm still trying to learn subqueries, but I came up with this.

SELECT A.ProdID, A.accnt, B.tot
FROM [SELECT ProdID, Count(CustID) AS accnt
FROM
(SELECT ProdID, CustID
FROM table3
GROUP BY ProdID, CustID) AS T
GROUP BY ProdID]. AS A INNER JOIN [SELECT ProdID, Sum(Quantity) AS
tot
FROM table3
GROUP BY ProdID]. AS B ON B.ProdID = A.ProdID;

Here are my results, I wonder that the difference in our results is an
error on your part or mine:

ProdID accnt tot
1001 2 30
1002 2 20
1004 3 20

Cheers,
Jason Lepack

Try this one

Lets say I have a few tables;

table1 Customers: CustomerID, Name,Address,..
table2: Products: ProductID, Description, Cost
table3: Transactions: CustomerID, ProdID, Quantity

with the following dataset:
CustID ProdID Quantity

BOB1 1001 10
BOB1 1002 5
JOE1 1004 5
SMS 1001 10
SMS 1002 10
SMS 1004 5
BOB1 1001 10
SMS 1002 5
BOB1 1004 10

If I run a totals query based on ProdID to show performance of each item;
...
Select ProdID, Sum(Quantity) as Total From table3 Group By ProdID

I can get the following:
ProdID Total
1001 30
1002 10
1004 20

But what if I want to know number of "distinct" accounts that were part
of
that calculation... e.g.
ProdID Total Accts
1001 30 2 ... 2 Bobs + 1 SMS or 2 separate
accounts
1002 10 2 ...(1 BOB) + (1 SMS) or 2 separate
accounts
1004 10 3 ...(1 BOB) + (1 SMS) + (1 JOE) or
3
separate accounts

How do I accomplish this?

I tried entering in the totals query, a field value of - Accts:
Count(Select Distinct ProdID from table 3). The failure message I get is
"At most one record can be returned from this subquery"

TRY 2:
SELECT DISTINCT table3.ProdID,
Sum(table3.Quantity AS [Total],
Count(A.StoreNumber) AS StoresReporting

FROM table3 INNER JOIN table3 AS A
ON table3.ItemNumber = A.ItemNumber
GROUP BY tblCalculatedSalesDataHomeDepot.ItemNumber;

This one jus ran forever without any result.. It seemed to hang up.

CAN SOMEONE PLEASE TELL ME HOW TO DO THIS???

 

[Gary Walter]

"John T Ingato"wrote

Lets say I have a few tables;

table1 Customers: CustomerID, Name,Address,..
table2: Products: ProductID, Description, Cost
table3: Transactions: CustomerID, ProdID, Quantity

with the following dataset:
CustID ProdID Quantity

BOB1 1001 10
BOB1 1002 5
JOE1 1004 5
SMS 1001 10
SMS 1002 10
SMS 1004 5
BOB1 1001 10
SMS 1002 5
BOB1 1004 10

If I run a totals query based on ProdID to show performance of each item;
...
Select ProdID, Sum(Quantity) as Total From table3 Group By ProdID

I can get the following:
ProdID Total
1001 30
1002 10
1004 20

But what if I want to know number of "distinct" accounts that were part of
that calculation... e.g.
ProdID Total Accts
1001 30 2 ... 2 Bobs + 1 SMS or 2 separate
accounts
1002 10 2 ...(1 BOB) + (1 SMS) or 2 separate
accounts
1004 10 3 ...(1 BOB) + (1 SMS) + (1 JOE) or 3
separate accounts

How do I accomplish this?

<snip>

Crosstab queries can be your friend...

TRANSFORM Sum(Table3.Quantity) AS SumCustID
SELECT
Table3.ProdID,
Sum(Table3.Quantity) AS Total,
Count([SumCustID]) AS Accts
FROM Table3
GROUP BY Table3.ProdID
PIVOT Table3.CustID;

from your example data, result would be:

ProdID Total Accts BOB1 JOE1 SMS
1001 30 2 20 10
1002 20 2 5 15
1004 20 3 10 5 5

or:

TRANSFORM First(Table3.CustID) AS DCustID
SELECT
Table3.ProdID,
Sum(Table3.Quantity) AS Total,
Count(DCustID) AS Accts
FROM Table3
GROUP BY Table3.ProdID
PIVOT CustID IN (NULL);

from your example data, result would be:

ProdID Total Accts <>
1001 30 2
1002 20 2
1004 20 3

the "<>" caused by "IN (NULL)"

w/o that in PIVOT clause:
(maybe easier to see what's going on)

TRANSFORM First(Table3.CustID) AS DCustID
SELECT
Table3.ProdID,
Sum(Table3.Quantity) AS Total,
Count(DCustID) AS Accts
FROM Table3
GROUP BY Table3.ProdID
PIVOT CustID;

from your example data, result would be:

ProdID Total Accts BOB1 JOE1 SMS
1001 30 2 BOB1 SMS
1002 20 2 BOB1 SMS
1004 20 3 BOB1 JOE1 SMS