Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old October 16th, 2006, 04:26 PM
Authorized User
 
Join Date: Oct 2006
Location: , , .
Posts: 10
Thanks: 0
Thanked 1 Time in 1 Post
Default sql query to generate counts by month

I have a database table with a bunch of transactions. Each transaction has a company and date associated with it. I want to generate a table which has 12 columns, one for each month, and a row for every company. I want each entry to be the number of transactions in the column month with the row company. Also a 13th column with the total for the year for the company, and a final row with the total for the column month across all companies.

Simple little spreadsheet, but I can't figure out how to get there. I think I need to GROUP BY the companies. GROUP BY Company and SELECT COUNT(Transaction number) will give me one column with all the transactions of the company ever. I really need a count function which counts only those matching some month.

Can you help me?

I'm using MS SQL 2000.

thanks in advance
Reply With Quote
The Following User Says Thank You to lethe For This Useful Post:
moorecreative (April 11th, 2015)
  #2 (permalink)  
Old October 16th, 2006, 04:56 PM
Friend of Wrox
Points: 2,101, Level: 18
Points: 2,101, Level: 18 Points: 2,101, Level: 18 Points: 2,101, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

Peso has written a very fine function that should do the trick for you. I've been able to use it for a number of date interval/period queries and calculations.

http://www.sqlservercentral.com/colu...tiliretire.asp



Reply With Quote
  #3 (permalink)  
Old October 16th, 2006, 11:33 PM
Friend of Wrox
Points: 1,533, Level: 15
Points: 1,533, Level: 15 Points: 1,533, Level: 15 Points: 1,533, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 474
Thanks: 0
Thanked 9 Times in 9 Posts
Default

You can do some things with "GROUPING" (not included below) to make the subtotals and totals more apparent but, considering you posted no schema nor data, this should get you started once you make the appropriate substitutions for table and column names as required...

 SELECT d.Company,
        d.[Year],
        SUM(CASE WHEN [Month] = 01 THEN Transactions ELSE 0 END) AS Jan,
        SUM(CASE WHEN [Month] = 02 THEN Transactions ELSE 0 END) AS Feb,
        SUM(CASE WHEN [Month] = 03 THEN Transactions ELSE 0 END) AS Mar,
        SUM(CASE WHEN [Month] = 04 THEN Transactions ELSE 0 END) AS Apr,
        SUM(CASE WHEN [Month] = 05 THEN Transactions ELSE 0 END) AS May,
        SUM(CASE WHEN [Month] = 06 THEN Transactions ELSE 0 END) AS Jun,
        SUM(CASE WHEN [Month] = 07 THEN Transactions ELSE 0 END) AS Jul,
        SUM(CASE WHEN [Month] = 08 THEN Transactions ELSE 0 END) AS Aug,
        SUM(CASE WHEN [Month] = 09 THEN Transactions ELSE 0 END) AS Sep,
        SUM(CASE WHEN [Month] = 10 THEN Transactions ELSE 0 END) AS Oct,
        SUM(CASE WHEN [Month] = 11 THEN Transactions ELSE 0 END) AS Nov,
        SUM(CASE WHEN [Month] = 12 THEN Transactions ELSE 0 END) AS [Dec],
        SUM(Transactions) AS Total,
        SUM(Transactions)/24 AS Average
   FROM (--Derived table "d" finds count for year and month
         SELECT Company,
                [Year] = DATEPART(yy,TransactionDate),
                [Month] = DATEPART(mm,TransactionDate)
                Transactions = COUNT(*)
           FROM yourtable
          GROUP BY Company,
                   DATEPART(yy,TransactionDate),
                   DATEPART(mm,TransactionDate)
        ) d
  GROUP BY d.Company,
           d.[Year]
   WITH ROLLUP
  ORDER BY d.Company,
           d.[Year]

...and, in a properly indexed environment, will process millions of rows in scant seconds (ie. 4 million rows in about 5-7 seconds).

--Jeff Moden
Reply With Quote
The Following User Says Thank You to Jeff Moden For This Useful Post:
moorecreative (April 11th, 2015)
  #4 (permalink)  
Old October 19th, 2006, 11:44 AM
Authorized User
 
Join Date: Oct 2006
Location: , , .
Posts: 10
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by Jeff Moden


...and, in a properly indexed environment, will process millions of rows in scant seconds (ie. 4 million rows in about 5-7 seconds).

--Jeff Moden
Hi Jeff-

That was a really cool SQL example. Much more sophisticated than anything I was trying. I've learned some stuff by playing with it and trying to understand it (for example, I didn't know that you can SELECT ... FROM (SELECT FROM ...) AS tablevariable query).

Also, I've never used WITH ROLLUP before. I read some pages on the internet to try to understand what it does, but it's kind of moot; I couldn't get my server to accept any of my WITH ROLLUP examples (including the one you posted). Is there something special about the query syntax which must be satisfied to use WITH ROLLUP? My server just says "Incorrect syntax near WITHROLLUP" for example with the query you posted. Do I have to have a GROUPING statement in the query?

If I remove the WITH ROLLUP from your query, I get the table I want with one problem; some of my rows have Year = NULL and then all of the month counts are zero. These rows have some nonzero number in their Total count column. I don't understand where these nonzero numbers come from. Is it a by product of how GROUP BY works? It's not that big a deal, but I'm just trying to understand how all this works.

Anyway, thanks for your help. If I get my problems resolved, I will post back. And if you feel like giving further advice, please do. Once again, thanks for your post, Jeff, which was quite helpful.

Reply With Quote
  #5 (permalink)  
Old October 19th, 2006, 06:49 PM
Friend of Wrox
Points: 1,533, Level: 15
Points: 1,533, Level: 15 Points: 1,533, Level: 15 Points: 1,533, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 474
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Lethe,

If you were to post the table schema, some example data (hopefully in the form of inserts), and the offending data, I'd be happy to see why the rollup isn't working...

--Jeff Moden
Reply With Quote
  #6 (permalink)  
Old October 23rd, 2006, 11:38 AM
Authorized User
 
Join Date: Oct 2006
Location: , , .
Posts: 10
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by Jeff Moden
 Lethe,

If you were to post the table schema, some example data (hopefully in the form of inserts), and the offending data, I'd be happy to see why the rollup isn't working...

--Jeff Moden
Hi Jeff-

What does it mean, table schema? You want me to say the name of the columns and their data type? Well, I'm working with an INNER JOIN of 3 or 4 pretty big tables, but I'm really only interested in three or four columns. They are TransactionNumber, Company, Owner, Date. There are a bunch of attributes that I'll to some where clauses with too.

Anyway, the following code seems to do what I want:

Code:
SELECT CASE WHEN (GROUPING(Company) = 1) THEN 'Company Total' ELSE Company END, 
COUNT(CASE WHEN DATEPART(month, Date) = 1 THEN TransactionNumber ELSE NULL END) AS 'Jan 2006',
.
.
.
COUNT(TransactionNumber) AS YearTotal
FROM  aTable
WHERE (Date >= '01/01/2006') AND (Date <= '12/31/2006')
GROUP BY Company WITH ROLLUP
I seem to have learned that WITH ROLLUP only works if your SELECT columns contain some GROUPING clauses, which is why I got syntax errors.

What do you think, is there a reason to prefer your code to mine?

Also, I don't like the way my YearTotal column works. I mean, it gives the correct number, but it seems like it would be logically preferable to tell this column to simply sum the values in the current row under the previous 12 columns. This is how you do things in Excel, for example. It has the benefit that if you change the months, the sum changes automatically. Can I do the same thing in SQL?
Reply With Quote
  #7 (permalink)  
Old October 23rd, 2006, 11:58 AM
Authorized User
 
Join Date: Oct 2006
Location: , , .
Posts: 10
Thanks: 0
Thanked 1 Time in 1 Post
Default

There's another thing I want to do with this table.

Before, I wanted to list summaries by CompanyName. Now I want to list them by transaction Owner. Basically I want my output to look like this:

Code:
Owner1  JanTotalForOwner            FebTotal ...
Cond1   TotalForOwnerMatchingCond1  FebTotal ...
Cond2  ...
Owner2 JanTotalOwner2AllConditions  Feb
Cond1
Cond2
Is it clear what I want? To me, it really looks like a three dimensional result set. Is that even possible in SQL?

Reply With Quote
  #8 (permalink)  
Old October 23rd, 2006, 10:51 PM
Friend of Wrox
Points: 1,533, Level: 15
Points: 1,533, Level: 15 Points: 1,533, Level: 15 Points: 1,533, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 474
Thanks: 0
Thanked 9 Times in 9 Posts
Default

I thought you understood that WITH ROLLUP was part of the GROUP BY clause... glad you figured that one out.

The other thing you asked was "To me, it really looks like a three dimensional result set. Is that even possible in SQL?" My answer would be, "only if you know how"... :D

They say that one picture is worth a thousand words... with that in mind...

Rather than working with your data (which I can't see), let's work with some data I know we both have so we can compare apples to apples. The following example uses the Northwind database that came with SQL Server 2000... you can, of course, modify the example to suit your own conditions and joins with your own data but at least we have some common ground with the Northwind database.

First, you asked "is there a reason to prefer your code to mine?" I'd have to say yes because your code is only good for one year. You've hard-coded the column names in that particular example with "MMM YYYY". You would need to change the code for every year you wanted to report on instead of just passing a single date or a single year you want to report on. Certainly, there's no real chance of a "3 dimensional" report when you hard-code such things.

Yes, you can do "3 dimensional" reporting, as you'll soon see... just add more column names to group on and add more GROUPING clauses to correctly identify the subtotals which are an element of the dimensions you ask for. Each added column to group on would be what you are calling "conditions". Keep in mind that if you don't do a CASE/GROUPING on each of those added columns, NULLs will appear in the column at group breaks caused by the ROLLUP.

In the example below, the "ShipCountry" would equate to "Owner1", "Owner2", etc. The "Year" would be "Cond1", "Cond2" etc. And notice how the "Owner" (ShipCountry) and "Condition" (Year) are programable separately and together?

One final thing.... these complex 3 dimensional reports are most easily accomplished by creating a query to give you the data you want in a vertical fashion first. That's where the "derived table" comes in in the code below. You write the query and test the hell out of it. When it's done, then you can concentrate on the creation of the crosstab report. In case you didn't know, a "derived table" is nothing more than a NON-correlated sub-query (a regular SELECT) that has parenthesis around it, has been given a table alias, and is used in the FROM clause of another query as if it's results were actually coming from a table.

If you have any questions after testing/studying the code below, post back... oh yeah... almost forgot... run the code below from Query Analyzer in the "Text" mode instead of the "Grid" mode to get the full impact of what this report looks like...

--===== Define the database to use
    USE Northwind

--===== Suppress the auto-display of rowcounts for appearance and speed
    SET NOCOUNT ON

--===== Declare some local control variables
     -- These could be parameters in a stored proc
DECLARE @Year VARCHAR(8)
DECLARE @Country VARCHAR(30)

--===== Change these to vary the output
    SET @Year = 'ALL' --'ALL' returns all years or change to a 4 digit year here
    SET @Country = 'ALL' --'ALL' returns all countries or change to a valid country name here

--===== Product the SUM of freight by country, year, and month report
 SELECT ShipCountry = CASE --Changes country name to 'Grand Total' when appropriate
                        WHEN GROUPING(ShipCountry) = 1
                         AND GROUPING([Year]) = 1
                        THEN 'GrandTotal'
                        ELSE ShipCountry
                      END,
        [Year] = CASE --Changes [Year] to 'SubTotal' or blank when appropriate
                        WHEN GROUPING(ShipCountry) = 0
                         AND GROUPING([Year]) = 0
                        THEN [Year]
                        WHEN GROUPING(ShipCountry) = 0
                         AND GROUPING([Year]) = 1
                        THEN 'SubTotal'
                        ELSE ' '
                      END,
        ' Jan' = STR(SUM(CASE WHEN [Month] = 01 THEN Freight ELSE 0 END),8,2),
        ' Feb' = STR(SUM(CASE WHEN [Month] = 02 THEN Freight ELSE 0 END),8,2),
        ' Mar' = STR(SUM(CASE WHEN [Month] = 03 THEN Freight ELSE 0 END),8,2),
        ' Apr' = STR(SUM(CASE WHEN [Month] = 04 THEN Freight ELSE 0 END),8,2),
        ' May' = STR(SUM(CASE WHEN [Month] = 05 THEN Freight ELSE 0 END),8,2),
        ' Jun' = STR(SUM(CASE WHEN [Month] = 06 THEN Freight ELSE 0 END),8,2),
        ' Jul' = STR(SUM(CASE WHEN [Month] = 07 THEN Freight ELSE 0 END),8,2),
        ' Aug' = STR(SUM(CASE WHEN [Month] = 08 THEN Freight ELSE 0 END),8,2),
        ' Sep' = STR(SUM(CASE WHEN [Month] = 09 THEN Freight ELSE 0 END),8,2),
        ' Oct' = STR(SUM(CASE WHEN [Month] = 10 THEN Freight ELSE 0 END),8,2),
        ' Nov' = STR(SUM(CASE WHEN [Month] = 11 THEN Freight ELSE 0 END),8,2),
        ' Dec' = STR(SUM(CASE WHEN [Month] = 12 THEN Freight ELSE 0 END),8,2),
        ' Total' = STR(SUM(Freight),8,2),
        ' ' = CASE WHEN GROUPING([Year])=1 THEN CHAR(13)+CHAR(13) ELSE '' END --Makes group breaks at sub-totals
   FROM (--Derived table does all the necessary conversions and pre-totals
         SELECT ShipCountry,
                [Year] = CAST(YEAR(OrderDate) AS CHAR(8)),
                [Month] = MONTH(OrderDate),
                Freight = SUM(Freight)
           FROM dbo.Orders
          WHERE YEAR(OrderDate) = CASE
                                    WHEN @Year = 'ALL' THEN STR(YEAR(OrderDate),8)
                                    ELSE @Year
                                  END
            AND ShipCountry = CASE
                                    WHEN @Country = 'ALL'
                                    THEN ShipCountry
                                    ELSE @Country
                                  END
          GROUP BY ShipCountry, CAST(YEAR(OrderDate) AS CHAR(8)),MONTH(OrderDate)
        ) d --End derived table
  GROUP BY ShipCountry,[Year] WITH ROLLUP
  ORDER BY GROUPING(ShipCountry),ShipCountry,GROUPING([Year]), [Year]


--Jeff Moden
Reply With Quote
The Following User Says Thank You to Jeff Moden For This Useful Post:
moorecreative (April 11th, 2015)
  #9 (permalink)  
Old April 11th, 2015, 11:52 PM
Registered User
Points: 3, Level: 1
Points: 3, Level: 1 Points: 3, Level: 1 Points: 3, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2015
Posts: 1
Thanks: 3
Thanked 0 Times in 0 Posts
Default almost 10 years later and this is the perfect script!

WOW, Jeff, looks like you posted this back in 2006 and here I am in 2015 searching for the perfect script to serve as the basis for a report I need to generate showing client project hours monthly.

Your help here, and of course your awesome suggestion and write up for this excellent query is fantastically appreciated.

Thanks for being awesome!
- Ryan
Reply With Quote
  #10 (permalink)  
Old March 7th, 2017, 12:08 AM
Friend of Wrox
Points: 1,533, Level: 15
Points: 1,533, Level: 15 Points: 1,533, Level: 15 Points: 1,533, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 474
Thanks: 0
Thanked 9 Times in 9 Posts
Default Sorry I'm late...

I don't know why this forum doesn't send me an email any more but, Ryan, thank you for your feedback about two years ago. Much appreciated.
__________________
--Jeff Moden
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Converting month name to month number kalyanykk SQL Server 2005 7 August 19th, 2008 10:37 PM
Sql Query to generate months and year arul1984 SQL Server 2000 8 July 23rd, 2007 04:42 PM
query Current Month, Month+1, Month+2, Month+3 anterior Access 2 September 24th, 2006 08:25 PM
Query birthdate by month Batusai Access 2 February 23rd, 2005 12:23 AM
efficiency qn xml vs sql server - to record counts ak Classic ASP Databases 3 March 3rd, 2004 04:26 PM



All times are GMT -4. The time now is 07:58 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.