Displaying data in columns and rows

Access or SQL Server?

SQL Server

Great -- I had a similar question in the SQL Server area that I think might help you out.  Check it out here and let me know if you have any questions about it!

https://www.experts-exchange.com/questions/21436301/Displaying-Data.html

Thanks,
Phil

Hi SurreyWebDesigner,

Is MonthStart a date field?

If so you could do this with a single query as follows:
  Select BookCategory,
    SUM(Count(Distinct BookID)*(1-ABS(SIGN(DatePart(mm,MonthStart)-1)))) as Jan,
    SUM(Count(Distinct BookID)*(1-ABS(SIGN(DatePart(mm,MonthStart)-2)))) as Feb,
    SUM(Count(Distinct BookID)*(1-ABS(SIGN(DatePart(mm,MonthStart)-3)))) as Mar,
    SUM(Count(Distinct BookID)*(1-ABS(SIGN(DatePart(mm,MonthStart)-4)))) as Apr,
    SUM(Count(Distinct BookID)*(1-ABS(SIGN(DatePart(mm,MonthStart)-5)))) as May,
    SUM(Count(Distinct BookID)*(1-ABS(SIGN(DatePart(mm,MonthStart)-6)))) as Jun,
    SUM(Count(Distinct BookID)*(1-ABS(SIGN(DatePart(mm,MonthStart)-7)))) as Jul,
    SUM(Count(Distinct BookID)*(1-ABS(SIGN(DatePart(mm,MonthStart)-8)))) as Aug,
    SUM(Count(Distinct BookID)*(1-ABS(SIGN(DatePart(mm,MonthStart)-9)))) as Sep,
    SUM(Count(Distinct BookID)*(1-ABS(SIGN(DatePart(mm,MonthStart)-10)))) as Oct,
    SUM(Count(Distinct BookID)*(1-ABS(SIGN(DatePart(mm,MonthStart)-11)))) as Nov,
    SUM(Count(Distinct BookID)*(1-ABS(SIGN(DatePart(mm,MonthStart)-12)))) as Dec
  From T_Books_MonthlySales ms InnerJoin
           T_BookCategory cat on cat.BookCategoryID = ms.BookCategoryID
  Group By BookCategory, MonthStart
  Order by MonthStart

This is called the Rozenshtein Method for doing a Cross-Tab query.  If MonthStart is not a date value, but is instead a number for the month (1 for Jan, 2 for Feb, etc...) then you can just replace the DatePart Section like the following for each line.
      SUM(Count(Distinct BookID)*(1-ABS(SIGN(MonthStart-1)))) as Jan

This will result in a recordset like this:

BookCategory   Jan   Feb  Mar  Apr  May  Jun  Jul  Aug  Sep  Oct  Nov  Dec
--------------------------------------------------------------------------------
Biography           xx    xx    xx    10    9    11    3    xx    xx    xx    xx    xx
Crime                 xx    xx    xx    11   13   19   10   xx    xx    xx    xx    xx
Fiction                xx    xx    xx    16   18    26  28   xx    xx    xx    xx    xx

The xx's will actually be numbers, but I don't know what those numbers are from your question.


Hope this helps,
Neal.

Hi nschafer,

Yes, MonthStart is a datetime field.

I've tried your suggestion but I get the following error message:

   "Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

Any ideas?

Thanks
SWD

I'll run a test.  I useually use this function on Qty fields.  It may be that we cannot sum a count. I'll run a quick test and post back.  

I'm not sure exactly how you are compiling the numbers actually.  Are you trying to get the total number of books sold in each category or the number of title's sold.  Counting Distinct BookID's I'm assuming is giving you the number of title's sold, not the number of books.  

In any case I'll run my test and post back.

Neal.

Hi Neal - the figures are coming from a table which collects info each month on how many books have been sold. This report will be used to view how many books have been sold for each category.

Many thanks for your help so far.

SWD

As I thought, the error was the result of running sum(count(...))  we can't sum count which is an agregrate function.  Sorry for not thinking of that in my initial post.  I can get around that this way

    SUM((BookID/BookID)*(1-ABS(SIGN(DatePart(mm,MonthStart)-1)))) as Jan,

assuming that BookID is a numeric field.

Let me explain how this works.

    SUM((BookID/BookID)*(1-ABS(SIGN(DatePart(mm,MonthStart)-1)))) as Jan,
                                     ^^^^^^^^^^^^^^^^^^^^^^^^^^^
The marked section will return a 0 if the month is not January or a 1 if it is January.  Each subsequent line is modified for the correct month.  

    SUM((BookID/BookID)*(1-ABS(SIGN(DatePart(mm,MonthStart)-1)))) as Jan,
            ^^^^^^^^^^
Normally with this code I am using a quantity field here and then we multiply that field by the 1 or 0 generated by the later part of the line and that will give us either the quantity field if it is for the correct month, or 0 if not for the correct month.  We then SUM all of the records with the SUM() function and get the correct value.  

COUNT doesn't work the same as SUM though.  With SUM() I can just calculate the number to a 0 and it is as if it doesn't exist, but with COUNT each record is counted regardless of its value.  So what I did here is I am taking a numeric field and dividing it by itself.  This will result in a value of 1.  Now I multiply that by the 0 or 1  generated in the later part of the line and each record now contains either a 0 or 1 for January.  I then sum these.  This gives us the same thing that count would, but split out by month.

It still seems odd to me to use a count function to determine the number of items sold, but I don't know your table structures or how the data gets into T_Books_MonthSales so I'll just assume you know what you need that and leave it at that.  The above modification to the query should resovle the error.

Neal.