Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: HAVING clause fails here

    Code:
    SELECT tblCARETSData.City, 'LISTINGENTRYDATE' AS count_type, Count(*) AS [count]
    FROM tblCARETSData
    WHERE (((tblCARETSData.ListingEntryDate) Between #1/1/2011# And #1/31/2011#))
    GROUP BY tblCARETSData.City
    HAVING(((tblCARETSData.city)="bd" Or "cthc" Or "coa" Or "dctr" Or "dhs" Or "ds" Or "iwld" Or "iw" Or "ind" Or "lq" Or "mec" Or "mntc" Or "pdst" Or "pspr" Or "rm" Or "salt" Or "sltb" Or "th" Or "tp"));
    This is returning hundreds of other city codes other than just the city codes in the HAVING clause.

    Thanks.

    Rick

  2. #2
    Join Date
    Jan 2011
    Posts
    14
    Can you please post your table definitions?

  3. #3
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    I figuered it out!

    Here . . .

    SELECT tblCARETSData.City, 'LISTINGENTRYDATE' AS count_type, Count(*) AS [count]
    FROM tblCARETSData
    WHERE (((tblCARETSData.ListingEntryDate) Between #1/1/2011# And #1/31/2011#)) AND ((tblCARETSData.City)="bd" Or (tblCARETSData.City)="cthc" Or (tblCARETSData.City)="coa" Or (tblCARETSData.City)="dctr" Or (tblCARETSData.City)="dhs" Or (tblCARETSData.City)="ds" Or (tblCARETSData.City)="iwld" Or (tblCARETSData.City)="iw" Or (tblCARETSData.City)="ind" Or (tblCARETSData.City)="lq" Or (tblCARETSData.City)="mec" Or (tblCARETSData.City)="mntc" Or (tblCARETSData.City)="pdst" Or (tblCARETSData.City)="pspr" Or (tblCARETSData.City)="rm" Or (tblCARETSData.City)="salt" Or (tblCARETSData.City)="sltb" Or (tblCARETSData.City)="th" Or (tblCARETSData.City)="tp")
    GROUP BY tblCARETSData.City
    ORDER BY tblCARETSData.City ASC
    ;

    Thanks . . . Rick

  4. #4
    Join Date
    Jan 2011
    Posts
    14
    I'm glad you solved it

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    clean it up a bit, rick...
    Code:
    SELECT City
         , 'LISTINGENTRYDATE' AS count_type
         , COUNT(*) AS [count]
      FROM tblCARETSData
     WHERE ListingEntryDate Between #1/1/2011# And #1/31/2011#
       AND City IN ('bd','cthc','coa','dctr','dhs','ds','iwld'
                   ,'iw','ind','lq','mec','mntc','pdst','pspr'
                   ,'rm','salt','sltb','th','tp' )
    GROUP 
        BY City
    ORDER 
        BY City ASC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Much Cleaner Rudy

    Ok how about this

    I wish to sort the count_type for each city.

    The count_type has three expressions(?) for each city . . .

    ListingEntryDate
    PendingDate and
    ClosingDate

    What I really could use is to FIRST sort results by City ASC - then the count_type exactly as the above order shows.

    What do you think?

    Rick

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    should be fairly straightforward...

    can you tell me how each of those types is calculated?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Sort count_type

    Like this . . .
    Code:
    City	count_type	          count
    BD	LISTINGENTRYDATE	28
    BD	PENDINGDATE	              18
    BD	closingDATE	              13
    COA	LISTINGENTRYDATE	43
    COA	PENDINGDATE	              37
    COA	closingDATE	              31
    CTHC	closingDATE	              38
    CTHC	LISTINGENTRYDATE	114
    CTHC	PENDINGDATE	              61
    DCTR	LISTINGENTRYDATE	1
    DHS	PENDINGDATE	              66
    DHS	closingDATE	              58
    DHS	LISTINGENTRYDATE	100
    IND	LISTINGENTRYDATE	214
    IND	PENDINGDATE	              103
    IND	closingDATE	               88
    Uh . . . you created most if not all of the original script Rudy.

    Thanks much.

    Rick

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, what i was asking was, you showed this --

    ... ListingEntryDate Between #1/1/2011# And #1/31/2011#

    and i was wondering if you wanted the others calculated the same way

    on the assumption that you do, try this --
    Code:
    SELECT City
         , 'LISTINGENTRYDATE' AS count_type
         , COUNT(*) AS [count]
      FROM tblCARETSData
     WHERE ListingEntryDate Between #1/1/2011# And #1/31/2011#
       AND City IN ('bd','cthc','coa','dctr','dhs','ds','iwld'
                   ,'iw','ind','lq','mec','mntc','pdst','pspr'
                   ,'rm','salt','sltb','th','tp' )
    GROUP 
        BY City
    UNION ALL
    SELECT City
         , 'PENDINGDATE' AS count_type
         , COUNT(*) AS [count]
      FROM tblCARETSData
     WHERE PendingDate Between #1/1/2011# And #1/31/2011#
       AND City IN ('bd','cthc','coa','dctr','dhs','ds','iwld'
                   ,'iw','ind','lq','mec','mntc','pdst','pspr'
                   ,'rm','salt','sltb','th','tp' )
    GROUP 
        BY City
    UNION ALL
    SELECT City
         , 'CLOSINGDATE' AS count_type
         , COUNT(*) AS [count]
      FROM tblCARETSData
     WHERE ClosingDate Between #1/1/2011# And #1/31/2011#
       AND City IN ('bd','cthc','coa','dctr','dhs','ds','iwld'
                   ,'iw','ind','lq','mec','mntc','pdst','pspr'
                   ,'rm','salt','sltb','th','tp' )
    GROUP 
        BY City
    ORDER
        BY City
         , IIF(count_type LIKE 'C*',1,0)
         , count_type
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Rick Schreiber View Post
    What I really could use is to FIRST sort results by City ASC - then the count_type exactly as the above order shows.
    by th way, your sample data does not reflect this requirement

    within various cities, you show

    LISTINGENTRYDATE, PENDINGDATE, closingDATE

    closingDATE, LISTINGENTRYDATE, PENDINGDATE

    PENDINGDATE, closingDATE, LISTINGENTRYDATE


    just sayin'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    error

    The ORDER BY Expression IIF (count Like C etc includes fields that are not selected by the query.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try it with just City in the ORDER BY clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    Doesn't work.

    Some of the Cities count_type start with pendingdate then closingdate then listingentrydat

  14. #14
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Interesting count_type Order

    City count_type count
    MNTC PENDINGDATE 1
    MNTC CLOSINGDATE 1
    MNTC LISTINGENTRYDATE 4
    PDST LISTINGENTRYDATE 272
    PDST PENDINGDATE 100
    PDST CLOSINGDATE 75
    PSPR CLOSINGDATE 79
    PSPR LISTINGENTRYDATE 238
    PSPR PENDINGDATE 111
    RM PENDINGDATE 34
    RM CLOSINGDATE 29
    RM LISTINGENTRYDATE 131
    TH LISTINGENTRYDATE 3
    TH PENDINGDATE 4
    TH CLOSINGDATE 2
    TP CLOSINGDATE 3
    TP LISTINGENTRYDATE 13
    TP PENDINGDATE 7

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Rick Schreiber View Post
    Doesn't work.
    oh ye of little faith

    watch as i make changes to the query ...
    Code:
    SELECT City
         , 'LISTINGENTRYDATE' AS count_type
         , 1 AS sort_order
         , COUNT(*) AS [count]
      FROM tblCARETSData
     WHERE ListingEntryDate Between #1/1/2011# And #1/31/2011#
       AND City IN ('bd','cthc','coa','dctr','dhs','ds','iwld'
                   ,'iw','ind','lq','mec','mntc','pdst','pspr'
                   ,'rm','salt','sltb','th','tp' )
    GROUP 
        BY City
    UNION ALL
    SELECT City
         , 'PENDINGDATE' AS count_type
         , 2 AS sort_order
         , COUNT(*) AS [count]
      FROM tblCARETSData
     WHERE PendingDate Between #1/1/2011# And #1/31/2011#
       AND City IN ('bd','cthc','coa','dctr','dhs','ds','iwld'
                   ,'iw','ind','lq','mec','mntc','pdst','pspr'
                   ,'rm','salt','sltb','th','tp' )
    GROUP 
        BY City
    UNION ALL
    SELECT City
         , 'CLOSINGDATE' AS count_type
         , 3 AS sort_order
         , COUNT(*) AS [count]
      FROM tblCARETSData
     WHERE ClosingDate Between #1/1/2011# And #1/31/2011#
       AND City IN ('bd','cthc','coa','dctr','dhs','ds','iwld'
                   ,'iw','ind','lq','mec','mntc','pdst','pspr'
                   ,'rm','salt','sltb','th','tp' )
    GROUP 
        BY City
    ORDER
        BY City
         , sort_order
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •