423,350 Members | 2,519 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,350 IT Pros & Developers. It's quick & easy.

Query to sum sales by SKU & portion of date

P: 1
I am trying to write an Access query that will report sales by SKU for a given month. The data table has the following columns:

Expand|Select|Wrap|Line Numbers
  1. OrderNumber    OrderDate        SKU     Title        QuantityShipped
  2. 10             1/23/2016        XXXX    Product X       1
  3. 11             3/12/2016        XXXX    Product X       1
  4. 12             10/2/2017        XXXX    Product X       1
  5. 13             12/22/2017       XXXX    Product X       1
  6. 14             4/25/2018        XXXX    Product X       3
  7. 15             4/27/2018        XXXX    Product X       1
  8. 16             1/15/2016        YYYY    Product Y       2
  9. 17             5/7/2017         YYYY    Product Y       1
  10. 18             5/18/2017        YYYY    Product Y       1
  11. 19             3/29/2018        YYYY    Product Y       3
I need only one line per SKU per month/year combination, i.e. output similar to this:
Expand|Select|Wrap|Line Numbers
  1. SKU      Title          Date     TotalShipped
  2. XXXX     Product X     Jan-16     1
  3. XXXX     Product X     Mar-16     1
  4. XXXX     Product X     Oct-17     1
  5. XXXX     Product X     Dec-17     1
  6. XXXX     Product X     Apr-18     4
  7. YYYY     Product Y     Jan-16     2
  8. YYYY     Product Y     May-17     2
  9. YYYY     Product Y     Mar-18     3
I can't seem to tease out the date format using DATEPART and I can't seem to isolate the SKU's into one line. Any hlep would be appreciated.
Aug 17 '18 #1
Share this Question
Share on Google+
8 Replies


zmbd
Expert Mod 5K+
P: 5,279
toddi76,
Welcome to Bytes.com!

Normally we ask that you show your work; however, with some of the more complex things, or when the obvious isn't the easiest solution (red-herrings abound in Access - in this case, DatePart()!)... and I've done this type of summary dozens of times in the lab:
Expand|Select|Wrap|Line Numbers
  1. SELECT byte971242.SKU, byte971242.Title
  2.    , Format([Odate],"mmm-yy") AS OrdrDate
  3.    , Sum(byte971242.Qty) AS SumOfQty
  4. FROM byte971242
  5. GROUP BY byte971242.SKU
  6.    , byte971242.Title
  7.    , Format([Odate],"mmm-yy")
  8. ORDER BY First(byte971242.Title)
  9.    , First(byte971242.Odate);
I used this thread's id as a generic table name.
Shortened the [Title] entries a tad for the example :)
Instead of the DatePart() used the simple Format()
should give you (I prefer a 4 digit year the Above SQL will return the 2 digit year as shown in your OP):


The sort order is the tricky part :)
Here I used the [Title]; however, you can just as easily use the [SKU] depending on your requirement/preference.
Attached Images
File Type: jpg 971242_SumQ.JPG (28.5 KB, 70 views)
Aug 18 '18 #2

NeoPa
Expert Mod 15k+
P: 31,007
If you use Format([ODate],'yyyymm') for your ORDER BY clause you should find it a little more straightforward.

You must use the same expression in the GROUP BY clause as you do in the SELECT one or it'll complain.
Aug 18 '18 #3

zmbd
Expert Mod 5K+
P: 5,279
NeoPa,
You are absolutely correct in the GROUP BY; however, the sort is happening in the ORDER BY so there is a bit of SQL-slight of hand being pulled here!

When you use the Format([ODate],'yyyymm') in place of the ORDER BY we then get the alphabetical sort by the Month name of [ODate] instead of the numerical Date sort - Tried this very early on for my lab reports - almost enough to drive a Chemist crazy... as if one could tell the difference @;@ ;

thus,

Either
Expand|Select|Wrap|Line Numbers
  1. SELECT t_byte971242.SKU, t_byte971242.Title, Format([Odate],"mmm-yyyy") AS OrdrDate
  2.   , Sum(t_byte971242.Qty) AS SumOfQty
  3. FROM t_byte971242
  4. GROUP BY t_byte971242.SKU
  5.   , t_byte971242.Title
  6.   , Format([Odate],"mmm-yyyy")
  7. ORDER BY First(t_byte971242.Title)
  8. , First(Format([Odate],"mmm-yyyy"));
OR

Expand|Select|Wrap|Line Numbers
  1. SELECT t_byte971242.SKU, t_byte971242.Title
  2.    , Format([Odate],"mmm-yyyy") AS OrdrDate
  3.    , Sum(t_byte971242.Qty) AS SumOfQty
  4. FROM t_byte971242
  5. GROUP BY t_byte971242.SKU, t_byte971242.Title
  6.    , Format([Odate],"mmm-yyyy")
  7.    , t_byte971242.Title, Format([Odate],"mmm-yyyy")
  8. ORDER BY t_byte971242.Title
  9. , Format([Odate],"mmm-yyyy");
Yield the same alphabetical sort by month name instead of by numerical value (well, sort of... with this data a bit random - ):


Even with:
ORDER BY First(t_byte971242.Title), First(Format([Odate],"mmm-yyyy"));
the alphabetical sort on the month name happens.

As far as the Access-SQL parser is concerned the Format() is returning a string value

However, in VBA - the parser can be told to accept "Jan-2018" as a date value and all is well:




Attached the DB that the screen shots were taken from in the previous post (#2) :)
Attached Images
File Type: jpg 971242_OrdByWFrmt.JPG (28.0 KB, 57 views)
File Type: jpg 971242_POC_DateValue.JPG (38.4 KB, 56 views)
Attached Files
File Type: zip Bytes971242.zip (19.3 KB, 1 views)
Aug 18 '18 #4

NeoPa
Expert Mod 15k+
P: 31,007
Hi Z.
I think you may need to check you're doing that right. Sorting by yyyymm sorts numerically by month - not alphabetically.
LMK if you're still getting wrong results after following my earlier instructions carefully.
Aug 20 '18 #5

zmbd
Expert Mod 5K+
P: 5,279
silly ole bear - I didn't catch the "YYYY" first and the change to "mm" - maybe I need more sleep, or honey.
Expand|Select|Wrap|Line Numbers
  1. SELECT t_byte971242.SKU, t_byte971242.Title
  2. Format([Odate],"mmm-yyyy") AS OrdrDate, Sum(t_byte971242.Qty) AS SumOfQty
  3. FROM t_byte971242
  4. GROUP BY t_byte971242.SKU, t_byte971242.Title
  5. , Format([Odate],"mmm-yyyy"),
  6. Format([Odate],"yyyymm")
  7. ORDER BY First(t_byte971242.Title)
  8. , Format([Odate],"yyyymm");
I also tried with leaving the "First" so that the format by YYYYMM didn't appear in the group by - notice it's not in the select clause... curious that.
does indeed return the numeric sort as the previous...
Aug 20 '18 #6

NeoPa
Expert Mod 15k+
P: 31,007
ZMBD:
I also tried with leaving the "First" so that the format by YYYYMM didn't appear in the group by - notice it's not in the select clause... curious that.
Check back to my earlier post (#3) where I explain why certain things need to go where.

PS. I just looked again and, though I didn't really understand what you were saying, I now think you found the yyyymm was also required in the GROUP BY clause. That's a pain. The reason will be to do with the fact that unless it's in there it doesn't know that it's the same data as the mmm-yyyy and thinks it isn't aggregated unless it's in there. As I say - a pain.

So, it turns out that it needs to be in the GROUP BY clause even if it's in the ORDER BY clause and not the SELECT clause.
Aug 20 '18 #7

zmbd
Expert Mod 5K+
P: 5,279
NeoPa,
Yet using my original SQL, the first([Odate]) only appears in the ORDER BY clause of the SQL - avoiding the potential "expanded" records (where everything shows up) issue. I stumbled on this a few years back - my guess is that because everything is being grouped at the month and year level then the "First" conditional simply looks at the first record in the aggregate and takes that value for the sort.
Either method works, is reproducible across different datasets, and appears to be reliable.


Re-reading this, I don't think I was very clear:
I also tried with leaving the "First" so that the format by YYYYMM didn't appear in the group by
What I intended to say was - leaving the format(yyyymm) in the first() we still get the proper time-based sort:

Expand|Select|Wrap|Line Numbers
  1. SELECT t_byte971242.SKU, t_byte971242.Title
  2. , Format([Odate],"mmm-yyyy") AS OrdrDate
  3. , Sum(t_byte971242.Qty) AS SumOfQty
  4. FROM t_byte971242
  5. GROUP BY 
  6. t_byte971242.SKU
  7. , t_byte971242.Title
  8. , Format([Odate],"mmm-yyyy")
  9. ORDER BY 
  10. First(t_byte971242.Title)
  11. First(Format([Odate],"yyyymm"));
  12. ^^^^^^^ Not in the Group Clause! ^^^^^^^
Aug 21 '18 #8

NeoPa
Expert Mod 15k+
P: 31,007
Yes Zmbd. That's a good solution. We know they're the same - and SQL frankly doesn't need to. Like it :-)
Aug 21 '18 #9

Post your reply

Sign in to post your reply or Sign up for a free account.