Sales reporting query for month and year

[Diemesse]

I'm writing a query that has me stumped. I work in Delphi, and am using an ADO query with Jet SQL (Access database). I need to know totals for the month in question, and also for the current year. How do I write a subquery to do this? I've tried:


SELECT Q.Manufacturer, Sum(Q.QValue) AS qval
FROM Q
WHERE (((Month([Q.QDate]))=8) AND ((Q.QDate)=2009))
GROUP BY Q.Manufacturer;


Works. Note that I'll use variables for the hard dates later. Now I'd like to add the total value for the year as well.


SELECT Q.Manufacturer, Sum(Q.QValue) AS qval, t1.qmaxtot
(SELECT Q.Manufacturer, max(Q.Qvalue) as Qmaxtot, from Q where [Q.QDate] = 2009 as t1)
FROM Q
WHERE (((Month([Q.QDate]))=8) AND ((Q.QDate)=2009))
GROUP BY Q.Manufacturer;


No go. Seems like it should be simple. Where am I going wrong?

Dave

 

[r937]

when you said your first query works, you actually meant that the query you tested works, because the query you posted cannot work, since [Q.QDate] isn't a valid column, whereas [Q].[QDate], Q.[QDate], and Q.QDate are

not picking on you personally, but this comes from over-exuberance in the use of brackets and parentheses, which is, sadly, quite common in Access

SELECT Manufacturer
     , SUM(IIF(Month(QDate)=8,QValue,0) AS qval
     , SUM(Qvalue) as Qmaxtot
  FROM Q
 WHERE YEAR(QDate) = 2009
GROUP 
    BY Manufacturer

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Diemesse]

You're right R937, probably wouldn't work. I've done light work in databases for 20 years, but never have been a huge SQL guy. I can get by with pretty simple queries most of the time. This was a working query in Delphi that I ported to Access for testing. (I stripped it down for posting to the forum.) Access put in the extra punctuation, so I just left it in there. Here's the actual working query:

SQL.Add('SELECT Manufacturer, ');
SQL.Add('sum(Qvalue) as qvalue, ');
SQL.Add('max(Qvalue) as Qmax, ');
SQL.Add('Min(Qvalue) as Qmin, ');
SQL.Add('avg(Qvalue) as Qavg, ');
SQL.Add('count(Manufacturer) as qcount ');
SQL.Add('from Quotes ');
SQL.Add('where Qvalue > 0 and month([QDate]) = 8 ');
SQL.Add('and [QDate] > #'+yeartext+'# ');
SQL.Add('group by manufacturer');

I know that there are a lot of SQL.Adds, but it makes it more readable for me. This is the stub that I'm working with now. My goal is to have these results for the month, as well as for a rolling year as well. As time goes on, I'll get better at SQL, but like most computer work, you have to jump off the dock sooner or later. Thanks in advance for bearing with me as I learn.

 

[Diemesse]

OK, I might be getting closer. I am trying to get numbers both for the month, and for a running 12 months off of this query. Comparatively, this will tell us how we are doing this month compared to our average month within the last 12 months. Here's my SQL.

Select * From 

(SELECT Manufacturer, 
sum(Qvalue) as qval, 
max(Qvalue) as Qmax, 
Min(Qvalue) as Qmin, 
avg(Qvalue) as Qavg, 
count(Manufacturer) as qcount 
from Quotes 
where Qvalue > 0 and month([QDate]) = 8 
and [QDate] > #2008/09/07# 
group by manufacturer ) Q1 

inner join 

(SELECT Manufacturer, 
sum(Qvalue) as qvaltotal, 
max(Qvalue) as Qmaxtotal, 
Min(Qvalue) as Qmintotal, 
avg(Qvalue) as Qavgtotal, 
count(Manufacturer) as qcounttotal 
from Quotes 
where Qvalue > 0 
and [QDate] > #2008/09/07# 
group by manufacturer ) Q2 

on Q1.manufacturer=Q2.manufacturer

Obviously, I need to tweak the 12 month running totals yet with a "between" function. My totals and averages look right
to me so far, and I'm using the max and min functions to tell me how wide my outliers are from the average.

Note that I am using Delphi, probably wouldn't work as listed in Access. I'm using an ADOQuery with Jet on an MDB database.

The inner join bothers me. If I don't have anything for the manufacturer for this month, he won't be included in the report. Is there a more elegant way to do this, or am I on the right path?

Dave

 

[r937]

to include stuff that may or may not have other stuff associated with it, use a LEFT OUTER JOIN

i'm guessing Q2 should be the left table, and Q1 the right

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Diemesse]

I changed to the left outer join, the query runs. I'll check the numbers to make sure I'm getting what I'm looking for.

Thanks for the help, Rudy. I've ordered your book on Amazon.

Dave