Need help creating a count for this query

C

Clint Champion

Here is my problem right now, my boss has asked me to create a Custom Sales
Report where the only customers displayed are the ones that have over 20
orders per month. My current idea is to count the number of invoice numbers
that this ODBC pulls out. And if i count over 20 invoice numbers than this
company will be displayed. Any ideas on how to do this or do something like
this, or how do implement a query like this.

Also, i do believe this is the worst and most unorganized database system
ive ever seen. Have to love the fact half the tables/queries do nothing at
all. Not to mention the data is spread out over to many queries and tables
its like untangleing Christmas lights you have been storing for years.

Any help is much appreciated, thank you.
 
K

Ken Sheridan

It can be done by means of a subquery. Lets say you have tables Customers
and Orders related on the CustomerID columns, and you want to return those
customers along with the total value of their orders where they have more
than 20 orders in a month which you enter (as the year and month as a number,
e.g. 2007 and 4 for this month) at runtime. The subquery would count orders
have been made in that month by the current customer in the outer query,
whose result set would be restricted to those customers wh have made over 20
orders:

SELECT CustomerID, CustomerName, OrderDate
SUM(OrderAmount) AS TotalOrderAmount
FROM Customers INNER JOIN Orders AS O1
ON O1.CustomerID = Customers.CustomerID
WHERE YEAR(OrderDate) = [Enter Year:]
AND MONTH(OrderDate) = [Enter Month:]
AND (SELECT COUNT(*)
FROM Orders AS O2
WHERE O2.CustomerID = O1.CustomerID
AND YEAR(O2.OrderDate) = YEAR(O1.OrderDate)
AND MONTH(O2.OrderDate) = MONTH(O1.OrderDate) > 20
GROUP BY CustomerID, CustomerName, OrderDate;

As another example, to return customers with more than 20 orders in *any*
month along with the total value of *all* their orders, could be done
slightly differently:

SELECT CustomerID, CustomerName, OrderDate
SUM(OrderAmount) AS TotalOrderAmount
FROM Customers INNER JOIN Orders AS O1
ON O1.CustomerID = Customers.CustomerID
WHERE EXISTS
(SELECT MONTH(OrderDate)
FROM Orders AS O2
WHERE O2.CustomerID = O1.CustomerID
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
HAVING COUNT(*) > 20)
GROUP BY CustomerID, CustomerName, OrderDate;

Ken Sheridan
Stafford, England
 
C

Clint Champion

Thank you for the help, It works perfectly.

Ken Sheridan said:
It can be done by means of a subquery. Lets say you have tables Customers
and Orders related on the CustomerID columns, and you want to return those
customers along with the total value of their orders where they have more
than 20 orders in a month which you enter (as the year and month as a number,
e.g. 2007 and 4 for this month) at runtime. The subquery would count orders
have been made in that month by the current customer in the outer query,
whose result set would be restricted to those customers wh have made over 20
orders:

SELECT CustomerID, CustomerName, OrderDate
SUM(OrderAmount) AS TotalOrderAmount
FROM Customers INNER JOIN Orders AS O1
ON O1.CustomerID = Customers.CustomerID
WHERE YEAR(OrderDate) = [Enter Year:]
AND MONTH(OrderDate) = [Enter Month:]
AND (SELECT COUNT(*)
FROM Orders AS O2
WHERE O2.CustomerID = O1.CustomerID
AND YEAR(O2.OrderDate) = YEAR(O1.OrderDate)
AND MONTH(O2.OrderDate) = MONTH(O1.OrderDate) > 20
GROUP BY CustomerID, CustomerName, OrderDate;

As another example, to return customers with more than 20 orders in *any*
month along with the total value of *all* their orders, could be done
slightly differently:

SELECT CustomerID, CustomerName, OrderDate
SUM(OrderAmount) AS TotalOrderAmount
FROM Customers INNER JOIN Orders AS O1
ON O1.CustomerID = Customers.CustomerID
WHERE EXISTS
(SELECT MONTH(OrderDate)
FROM Orders AS O2
WHERE O2.CustomerID = O1.CustomerID
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
HAVING COUNT(*) > 20)
GROUP BY CustomerID, CustomerName, OrderDate;

Ken Sheridan
Stafford, England

Clint Champion said:
Here is my problem right now, my boss has asked me to create a Custom Sales
Report where the only customers displayed are the ones that have over 20
orders per month. My current idea is to count the number of invoice numbers
that this ODBC pulls out. And if i count over 20 invoice numbers than this
company will be displayed. Any ideas on how to do this or do something like
this, or how do implement a query like this.

Also, i do believe this is the worst and most unorganized database system
ive ever seen. Have to love the fact half the tables/queries do nothing at
all. Not to mention the data is spread out over to many queries and tables
its like untangleing Christmas lights you have been storing for years.

Any help is much appreciated, thank you.
Click to expand...
Click to expand...
 
Top