Thread: SQL help

    #1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    1
    Rep Power
    0

    SQL help


    I have a file that looks like this...

    TYPE INV_DATE
    ____ ________
    MI 11/30/03
    MI 11/30/03
    MI 11/30/03
    NG 11/30/03
    WP 11/30/03
    CN 11/30/03
    MI 11/30/03
    WP 11/30/03
    NG 11/30/03
    MI 11/30/03



    I want to write a query that will allow me to get a count for the total occurance of each type within a date range. I was guessing something like this...

    select
    count(*) as total
    count(type-'MI') as moneyinvoice
    count(type='WP') as wip
    count(type='CN') as cancel
    from filename
    where inv_date>= '11/30/2003'

    I would expect the query answer to look like this:

    Total moneyinvoice wip cancel
    10 5 2 3




    can you help me with this? Thanks!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Location
    BC, Canada
    Posts
    32
    Rep Power
    11
    The parameter for count can only be a field name.

    You could do:

    DELCARE @EndDate DateTime
    SET @EndDate = '11/30/2003'

    SELECT
    COUNT(*) as total,
    (SELECT count(*) FROM filename WHERE type='MI' AND inv_date >= @EndDate) as moneyinvoice,
    (SELECT count(*) FROM filename WHERE type='WP' AND inv_date >= @EndDate) as wip,
    (SELECT count(*) FROM filename WHERE type='CN' AND inv_date >= @EndDate) as cancel
    FROM filename
    WHERE inv_date >= @EndDate

    or to make it look a little neater, but do the same:

    DELCARE @EndDate DateTime
    SET @EndDate = '11/30/2003'

    SELECT
    (SELECT count(*) FROM filename WHERE AND inv_date>= @EndDate) as total,
    (SELECT count(*) FROM filename WHERE type='MI' AND inv_date >= @EndDate) as moneyinvoice,
    (SELECT count(*) FROM filename WHERE type='WP' AND inv_date >= @EndDate) as wip,
    (SELECT count(*) FROM filename WHERE type='CN' AND inv_date >= @EndDate) as cancel
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    NY
    Posts
    18
    Rep Power
    0
    Code:
    SELECT 	COUNT(*) as Totals, 
    		CASE WHEN GROUPING(Type) = 1 THEN 'Total' 
    			ELSE Type 
    		END as Type
    	FROM dbo.FileName
    	GROUP BY Type WITH ROLLUP
    Quick to code and will not be limitted to only values you code for. Of course if you decide to return more complete names from the database (ie. moneyinvoice) you will want to add it into the second part of the case statement as another case statement.
    -- Messorian
    Last edited by messorian; December 4th, 2003 at 01:16 AM.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,140
    Rep Power
    4274
    another way:
    Code:
    select count(*) as total
         , sum(case when type = 'MI'
                    then 1 else 0 end ) as moneyinvoice
         , sum(case when type = 'WP'
                    then 1 else 0 end ) as wip
         , sum(case when type = 'CN'
                    then 1 else 0 end ) as cancel
      from filename
     where inv_date >= '11/30/2003'
    rudy
    http://r937.com/

IMN logo majestic logo threadwatch logo seochat tools logo