SQL Query for unique entries and sums by group

[Brady Mann - Amatuer Extraordinaire]

Gang,

I've read through many of the postings on this site and I'm getting
close (but have no cigar). Here is what I have in a single table (data
changed to keep it simple)

ID VendorName Parts QtyOrdered AvgPartCost
1 Fred ABC 1 $.05
2 Fred ABC 1 $.05
3 Fred ABB 2 $.05
4 Bob XYZ 2 $.05
5 Bob XYY 2 $.05

This is what I want from a Query:
VendorName Parts(Unique) TotalSpend
(QtyOrdered*AvgPartCost)
Fred 2 $.20
Bob 2 $.20

As you can see above, I want to determine the number of UNIQUE parts
purchased by VendorName. However, I want to SUM the total of all
purchases and group them by VendorName.

Thus far, I've been able to find the total unique parts by VendorName
with this SQL (note, the below is rather sloppy and I'm sure there is a
cleaner method):

***startSQL
SELECT [%$##@_Alias].VendorName, Count(*) AS Parts
FROM [SELECT DISTINCT
DatabaseQuery.VendorName,
DatabaseQuery.Parts
FROM
DatabaseQuery]. AS [%$##@_Alias]
GROUP BY [%$##@_Alias].VendorName;
***end SQL

I can get the total spend per supplier like this:

***start SQL
SELECT DatabaseQuery.VendorName, Sum(OrderedQty*AvgPartCost) AS
["TotalPurchase"]
FROM DatabaseQuery
GROUP BY DatabaseQuery.VendorName;
***end SQL

What I can't seem to do is get this all into one query. I've tried
using the UNION statement but...when I do that I get a TOTAL part count
instead of a unique part count (in my example above, the total parts
for "Fred" would be 3 instead of 2).

How can I put this calmly...HELP!!!!!!!!!!!!!!!!

 

[Douglas J Steele]

I'll show an approach that should work using a couple of queries. You should
be able to reduce it all to a single query, but I only have Access 97 on
this machine, so can't test.

Create a query (we'll name it "Query1") that unions your two queries:

SELECT [%$##@_Alias].VendorName,
Count(*) AS Parts,
0 AS ["TotalPurchase"]
FROM [SELECT DISTINCT
DatabaseQuery.VendorName,
DatabaseQuery.Parts
FROM
DatabaseQuery]. AS [%$##@_Alias]
GROUP BY [%$##@_Alias].VendorName
UNION
SELECT DatabaseQuery.VendorName,
0 AS Parts,
Sum(OrderedQty*AvgPartCost) AS ["TotalPurchase"]
FROM DatabaseQuery
GROUP BY DatabaseQuery.VendorName;

This should return something like:

VendorName Parts TotalPurchase
Bob 2 0
Bob 0 $.20
Fred 2 0
Fred 0 $.20

Now, sum that:

SELECT VendorName,
SUM(Parts) AS UniqueParts,
SUM(TotalPurchase) AS TotalSpent
FROM Query1
GROUP BY VendorName


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Gang,

I've read through many of the postings on this site and I'm getting
close (but have no cigar). Here is what I have in a single table (data
changed to keep it simple)

ID VendorName Parts QtyOrdered AvgPartCost
1 Fred ABC 1 $.05
2 Fred ABC 1 $.05
3 Fred ABB 2 $.05
4 Bob XYZ 2 $.05
5 Bob XYY 2 $.05

This is what I want from a Query:
VendorName Parts(Unique) TotalSpend
(QtyOrdered*AvgPartCost)
Fred 2 $.20
Bob 2 $.20

As you can see above, I want to determine the number of UNIQUE parts
purchased by VendorName. However, I want to SUM the total of all
purchases and group them by VendorName.

Thus far, I've been able to find the total unique parts by VendorName
with this SQL (note, the below is rather sloppy and I'm sure there is a
cleaner method):

***startSQL
SELECT [%$##@_Alias].VendorName, Count(*) AS Parts
FROM [SELECT DISTINCT
DatabaseQuery.VendorName,
DatabaseQuery.Parts
FROM
DatabaseQuery]. AS [%$##@_Alias]
GROUP BY [%$##@_Alias].VendorName;
***end SQL

I can get the total spend per supplier like this:

***start SQL
SELECT DatabaseQuery.VendorName, Sum(OrderedQty*AvgPartCost) AS
["TotalPurchase"]
FROM DatabaseQuery
GROUP BY DatabaseQuery.VendorName;
***end SQL

What I can't seem to do is get this all into one query. I've tried
using the UNION statement but...when I do that I get a TOTAL part count
instead of a unique part count (in my example above, the total parts
for "Fred" would be 3 instead of 2).

How can I put this calmly...HELP!!!!!!!!!!!!!!!!

 

[Brady Mann - Amatuer Extraordinaire]

The two queries worked! If I can figure out how to combine the two into
a single query, I'll post it here for others. Thanks for the help!

 

[Douglas J Steele]

I'd expect it to be something like:

SELECT VendorName,
SUM(Parts) AS UniqueParts,
SUM(TotalPurchase) AS TotalSpent
FROM
(SELECT [%$##@_Alias].VendorName,
Count(*) AS Parts,
0 AS TotalPurchase
FROM [SELECT DISTINCT
DatabaseQuery.VendorName,
DatabaseQuery.Parts
FROM
DatabaseQuery]. AS [%$##@_Alias]
GROUP BY [%$##@_Alias].VendorName
UNION
SELECT DatabaseQuery.VendorName,
0 AS Parts,
Sum(OrderedQty*AvgPartCost) AS TotalPurchase
FROM DatabaseQuery
GROUP BY DatabaseQuery.VendorName) AS SQ
GROUP BY VendorName