• Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
  • Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

SQL Query Help anyone??

Collapse
X
  •  
  • Filter
  • Time
  • Show
Clear All
new posts

    SQL Query Help anyone??

    Hi I am having problems using the Group by.
    My queory uses a union between 2 subqueries and I am trying to pull out those that appear more than 2 times can anyone help?
    I get a "Incorrect syntax near the keyword 'GROUP'" error when i try to run the query in Query Analyzer.
    Can you help at all
    ------------------------
    HERES THE QUERY;

    select accountno from contact1 where accountno in

    ((SELECT accountno FROM contsupp
    WHERE rectype = 'p' AND CAST(SUBSTRING(city,9,8) AS datetime)between '01/01/2006' and '04/30/2007'
    AND
    (contact LIKE '%Focus%'
    or contact LIKE '%Training%'
    or contact LIKE '%Course%'
    or contact LIKE '%HDI%'
    or contact LIKE '%Executive Forum%'
    or contact LIKE '%Benchmark buddy%'
    or contact LIKE '%Committee%'
    or contact LIKE '%Consultancy%'
    or contact LIKE '%Helpline%'
    or contact LIKE '%Leadership%'
    or contact LIKE '%Site Aud%'
    or contact LIKE '%Site Ass%'
    or contact LIKE '%Site Re%')
    UNION ALL
    SELECT accountno FROM GMSM_GMBASE.dbo.cal WHERE rectype = 'S'
    )GROUP BY accountno HAVING COUNT (*) >2)


    #2
    Try "GROUP BY accountno HAVING COUNT(accountno) > 2"
    Listen to my last album on Spotify

    Comment


      #3
      Unfortunately it results i the same message!

      Comment


        #4
        What DB? Just tested on one of my tables in DB2 and it doesn't work with the outer select. Does this work?

        Code:
        SELECT accountno FROM contsupp
        WHERE rectype = 'p' AND CAST(SUBSTRING(city,9,8) AS datetime)between '01/01/2006' and '04/30/2007'
        AND
        (contact LIKE '%Focus%'
        or contact LIKE '%Training%'
        or contact LIKE '%Course%'
        or contact LIKE '%HDI%'
        or contact LIKE '%Executive Forum%'
        or contact LIKE '%Benchmark buddy%'
        or contact LIKE '%Committee%'
        or contact LIKE '%Consultancy%'
        or contact LIKE '%Helpline%'
        or contact LIKE '%Leadership%'
        or contact LIKE '%Site Aud%'
        or contact LIKE '%Site Ass%'
        or contact LIKE '%Site Re%')
        UNION ALL
        SELECT accountno FROM GMSM_GMBASE.dbo.cal WHERE rectype = 'S'
        GROUP BY accountno HAVING COUNT (accountno) > 2;
        Listen to my last album on Spotify

        Comment


          #5
          Add a tablename just after you define your unioned tables, along the lines of

          or contact LIKE '%Site Ass%'
          or contact LIKE '%Site Re%')
          UNION ALL
          SELECT accountno FROM GMSM_GMBASE.dbo.cal WHERE rectype = 'S'
          ) Table1 GROUP BY accountno HAVING COUNT (*) >2)

          Oh, and as matey above said, use COUNT(AccountNo) not COUNT(*)

          Also make sure you have your indexes setup correctly, else using Like will slow the execution down somewhat!!!!

          And finally, change your date format to YYYYMMDD. Using DD/MM/YYYY is an easy way to make mistakes
          Last edited by Weltchy; 10 April 2007, 13:31.

          Comment


            #6
            Amateurs.
            What happens in General, stays in General.
            You know what they say about assumptions!

            Comment


              #7
              Looks like goldmine data which is a ****tard to work with on the best of days
              Coffee's for closers

              Comment


                #8
                Originally posted by MarillionFan
                Amateurs.


                Know-it-all!!!

                Comment

                Working...
                X