none
Having Count RRS feed

  • Question

  • Hi All,

    I am having trouble with a statement, i am trying to select all data from 2 tables which link together with an id. But i would like to only bring back ones where there is only 1 group. Hope this makes sense. So i am looking for 1 Master record which only has 1 duplicate, and then 1 master with 2 dupes, i have already flagged the dupes and linked to the master.

    This is what i am trying to acheive:

    SELECT Count(bd.dqlinkid) , bd.DQID, bd.ID, bd.Salutation, bd.[First Name], bd.[Middle Name], bd.Surname, bd.[Full Name], bd.Title, bd.[Phone number], bd.Email, bd.[Web Address], bd.Organisation, bd.[Address Line 1], bd.[Address Line 2], bd.[Address Line 3], bd.[Address Line 4], bd.[Post Town], bd.County, bd.Postcode, bd.Country, bd.[Invoice No], bd.Invtot, bd.VehicleRegNo, bd.Plant, bd.[Created on], bd.CarManufacturer, bd.[Car model], bd.CarManufactYear, bd.Mileage, bd.Customer, bd.[branch code], bd.[urn no], bd.success, bd.[branch type], bd.[Month], bd.Marked, bd.DQFlag, bd.DQLinkID, md.DQID, md.ID, md.Salutation, md.[First Name], md.[Middle Name], md.Surname, md.[Full Name], md.Title, md.[Phone number], md.Email, md.[Web Address], md.Organisation, md.[Address Line 1], md.[Address Line 2], md.[Address Line 3], md.[Address Line 4], md.[Post Town], md.County, md.Postcode, md.Country, md.[Invoice No], md.Invtot, md.VehicleRegNo, md.Plant, md.[Created on], md.CarManufacturer, md.[Car model], md.CarManufactYear, md.Mileage, md.Customer, md.[branch code], md.[urn no], md.success, md.[branch type], md.[month], md.Marked, md.DQFlag, md.DQLinkID
    
    FROM [Branch data 2009 - 2012] AS bd INNER JOIN [main mailing data] AS md ON bd.dqlinkid = md.dqlinkid
    
    GROUP BY bd.DQID, bd.ID, bd.Salutation, bd.[First Name], bd.[Middle Name], bd.Surname, bd.[Full Name], bd.Title, bd.[Phone number], bd.Email, bd.[Web Address], bd.Organisation, bd.[Address Line 1], bd.[Address Line 2], bd.[Address Line 3], bd.[Address Line 4], bd.[Post Town], bd.County, bd.Postcode, bd.Country, bd.[Invoice No], bd.Invtot, bd.VehicleRegNo, bd.Plant, bd.[Created on], bd.CarManufacturer, bd.[Car model], bd.CarManufactYear, bd.Mileage, bd.Customer, bd.[branch code], bd.[urn no], bd.success, bd.[branch type], bd.[Month], bd.Marked, bd.DQFlag, bd.DQLinkID, md.DQID, md.ID, md.Salutation, md.[First Name], md.[Middle Name], md.Surname, md.[Full Name], md.Title, md.[Phone number], md.Email, md.[Web Address], md.Organisation, md.[Address Line 1], md.[Address Line 2], md.[Address Line 3], md.[Address Line 4], md.[Post Town], md.County, md.Postcode, md.Country, md.[Invoice No], md.Invtot, md.VehicleRegNo, md.Plant, md.[Created on], md.CarManufacturer, md.[Car model], md.CarManufactYear, md.Mileage, md.Customer, md.[branch code], md.[urn no], md.success, md.[branch type], md.[month], md.Marked, md.DQFlag, md.DQLinkID
    
    HAVING (((Count(bd.dqlinkid))=2));

    All help is much appreciated, Thanks

    Wednesday, March 28, 2012 8:19 AM

Answers

All replies

  • Can you post sample data + desired result?

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Wednesday, March 28, 2012 8:26 AM
    Answerer
  • the desired result is to select a master which only links to 1 duplicate, then a master that links to 2 dupes ect up to 4 and above.

    how can i attach excel on here?

    Wednesday, March 28, 2012 8:35 AM
  • WITH myCTE AS
    (
    	-- Total Repeats will be the main record plus the dupes count
        SELECT COUNT(bd.dqlinkid) AS totalRepeats
              ,bd.DQID
              ,bd.ID
              ,bd.Salutation
              ,bd.[First Name]
              ,bd.[Middle Name]
              ,bd.Surname
              ,bd.[Full Name]
              ,bd.Title
              ,bd.[Phone number]
              ,bd.Email
              ,bd.[Web Address]
              ,bd.Organisation
              ,bd.[Address Line 1]
              ,bd.[Address Line 2]
              ,bd.[Address Line 3]
              ,bd.[Address Line 4]
              ,bd.[Post Town]
              ,bd.County
              ,bd.Postcode
              ,bd.Country
              ,bd.[Invoice No]
              ,bd.Invtot
              ,bd.VehicleRegNo
              ,bd.Plant
              ,bd.[Created on]
              ,bd.CarManufacturer
              ,bd.[Car model]
              ,bd.CarManufactYear
              ,bd.Mileage
              ,bd.Customer
              ,bd.[branch code]
              ,bd.[urn no]
              ,bd.success
              ,bd.[branch type]
              ,bd.[Month]
              ,bd.Marked
              ,bd.DQFlag
              ,bd.DQLinkID
              ,md.DQID
              ,md.ID
              ,md.Salutation
              ,md.[First Name]
              ,md.[Middle Name]
              ,md.Surname
              ,md.[Full Name]
              ,md.Title
              ,md.[Phone number]
              ,md.Email
              ,md.[Web Address]
              ,md.Organisation
              ,md.[Address Line 1]
              ,md.[Address Line 2]
              ,md.[Address Line 3]
              ,md.[Address Line 4]
              ,md.[Post Town]
              ,md.County
              ,md.Postcode
              ,md.Country
              ,md.[Invoice No]
              ,md.Invtot
              ,md.VehicleRegNo
              ,md.Plant
              ,md.[Created on]
              ,md.CarManufacturer
              ,md.[Car model]
              ,md.CarManufactYear
              ,md.Mileage
              ,md.Customer
              ,md.[branch code]
              ,md.[urn no]
              ,md.success
              ,md.[branch type]
              ,md.[month]
              ,md.Marked
              ,md.DQFlag
              ,md.DQLinkID
        FROM   [Branch data 2009 - 2012] AS bd
               INNER JOIN [main mailing data] AS md
                    ON  bd.dqlinkid = md.dqlinkid
        GROUP BY
               bd.DQID
              ,bd.ID
              ,bd.Salutation
              ,bd.[First Name]
              ,bd.[Middle Name]
              ,bd.Surname
              ,bd.[Full Name]
              ,bd.Title
              ,bd.[Phone number]
              ,bd.Email
              ,bd.[Web Address]
              ,bd.Organisation
              ,bd.[Address Line 1]
              ,bd.[Address Line 2]
              ,bd.[Address Line 3]
              ,bd.[Address Line 4]
              ,bd.[Post Town]
              ,bd.County
              ,bd.Postcode
              ,bd.Country
              ,bd.[Invoice No]
              ,bd.Invtot
              ,bd.VehicleRegNo
              ,bd.Plant
              ,bd.[Created on]
              ,bd.CarManufacturer
              ,bd.[Car model]
              ,bd.CarManufactYear
              ,bd.Mileage
              ,bd.Customer
              ,bd.[branch code]
              ,bd.[urn no]
              ,bd.success
              ,bd.[branch type]
              ,bd.[Month]
              ,bd.Marked
              ,bd.DQFlag
              ,bd.DQLinkID
              ,md.DQID
              ,md.ID
              ,md.Salutation
              ,md.[First Name]
              ,md.[Middle Name]
              ,md.Surname
              ,md.[Full Name]
              ,md.Title
              ,md.[Phone number]
              ,md.Email
              ,md.[Web Address]
              ,md.Organisation
              ,md.[Address Line 1]
              ,md.[Address Line 2]
              ,md.[Address Line 3]
              ,md.[Address Line 4]
              ,md.[Post Town]
              ,md.County
              ,md.Postcode
              ,md.Country
              ,md.[Invoice No]
              ,md.Invtot
              ,md.VehicleRegNo
              ,md.Plant
              ,md.[Created on]
              ,md.CarManufacturer
              ,md.[Car model]
              ,md.CarManufactYear
              ,md.Mileage
              ,md.Customer
              ,md.[branch code]
              ,md.[urn no]
              ,md.success
              ,md.[branch type]
              ,md.[month]
              ,md.Marked
              ,md.DQFlag
              ,md.DQLinkID
    )
    
    SELECT *
    FROM   myCTE
    WHERE  totalRepeats IN (2 ,3 ,4 ,5) 
    -- Total Repeats would be 1+ dupes count required.
    -- Or instead you can use UNION All on the CTE if you need additional criteria selectively.
           
    Hope this is what you are looking for.

    Welcome to MSDN Forums.
    Feel free to ask your questions and Please Note to Vote all helpful topics and Mark all answering posts.

    You Value Community's efforts and Community values your faith.
    - Arun Kumar Allu

    Wednesday, March 28, 2012 8:47 AM
  • nope that didnt work, got this error:

    Msg 8156, Level 16, State 1, Line 1
    The column 'DQID' was specified multiple times for 'myCTE'.

    Also if i do the select separatley i get this error:

    Msg 102, Level 15, State 1, Line 161
    Incorrect syntax near ')'.


    Wednesday, March 28, 2012 8:56 AM
  • Msg 8156, Level 16, State 1, Line 1
    The column 'DQID' was specified multiple times for 'myCTE'.

    You need to alias the column name if you use it second time in the CTE SELECT list (md.DQID), even though the table name is different.

    CTE article:

    http://www.sqlusa.com/bestpractices2005/cte/

    Example for column aliasing:

    ,md.DQID as mdDQID 
    
    Note, however, it is not the best design to repeat columns in a SELECT query.

    Kalman Toth SQL SERVER & BI TRAINING


    Wednesday, March 28, 2012 9:03 AM
    Moderator
  • SELECT Count(bd.dqlinkid) , bd.DQID, bd.ID, bd.Salutation, bd.[First Name], bd.[Middle Name], bd.Surname, bd.[Full Name], bd.Title, bd.[Phone number], bd.Email, bd.[Web Address], bd.Organisation, bd.[Address Line 1], bd.[Address Line 2], bd.[Address Line 3], bd.[Address Line 4], bd.[Post Town], bd.County, bd.Postcode, bd.Country, bd.[Invoice No], bd.Invtot, bd.VehicleRegNo, bd.Plant, bd.[Created on], bd.CarManufacturer, bd.[Car model], bd.CarManufactYear, bd.Mileage, bd.Customer, bd.[branch code], bd.[urn no], bd.success, bd.[branch type], bd.[Month], bd.Marked, bd.DQFlag, bd.DQLinkID, md.DQID, md.ID, md.Salutation, md.[First Name], md.[Middle Name], md.Surname, md.[Full Name], md.Title, md.[Phone number], md.Email, md.[Web Address], md.Organisation, md.[Address Line 1], md.[Address Line 2], md.[Address Line 3], md.[Address Line 4], md.[Post Town], md.County, md.Postcode, md.Country, md.[Invoice No], md.Invtot, md.VehicleRegNo, md.Plant, md.[Created on], md.CarManufacturer, md.[Car model], md.CarManufactYear, md.Mileage, md.Customer, md.[branch code], md.[urn no], md.success, md.[branch type], md.[month], md.Marked, md.DQFlag, md.DQLinkID
    FROM [Branch data 2009 - 2012] AS bd INNER JOIN [main mailing data] AS md ON bd.dqlinkid = md.dqlinkid
    GROUP BY bd.DQID, bd.ID, bd.Salutation, bd.[First Name], bd.[Middle Name], bd.Surname, bd.[Full Name], bd.Title, bd.[Phone number], bd.Email, bd.[Web Address], bd.Organisation, bd.[Address Line 1], bd.[Address Line 2], bd.[Address Line 3], bd.[Address Line 4], bd.[Post Town], bd.County, bd.Postcode, bd.Country, bd.[Invoice No], bd.Invtot, bd.VehicleRegNo, bd.Plant, bd.[Created on], bd.CarManufacturer, bd.[Car model], bd.CarManufactYear, bd.Mileage, bd.Customer, bd.[branch code], bd.[urn no], bd.success, bd.[branch type], bd.[Month], bd.Marked, bd.DQFlag, bd.DQLinkID, md.DQID, md.ID, md.Salutation, md.[First Name], md.[Middle Name], md.Surname, md.[Full Name], md.Title, md.[Phone number], md.Email, md.[Web Address], md.Organisation, md.[Address Line 1], md.[Address Line 2], md.[Address Line 3], md.[Address Line 4], md.[Post Town], md.County, md.Postcode, md.Country, md.[Invoice No], md.Invtot, md.VehicleRegNo, md.Plant, md.[Created on], md.CarManufacturer, md.[Car model], md.CarManufactYear, md.Mileage, md.Customer, md.[branch code], md.[urn no], md.success, md.[branch type], md.[month], md.Marked, md.DQFlag, md.DQLinkID
    HAVING Count(bd.dqlinkid) = 2 OR Count(bd.dqlinkid) = 3

    Try this..

    SELECT

    Count(bd.dqlinkid) , bd.DQID, bd.ID, bd.Salutation, bd.[First Name], bd.[Middle Name], bd.Surname, bd.[Full Name], bd.Title, bd.[Phone number], bd.Email, bd.[Web Address], bd.Organisation, bd.[Address Line 1], bd.[Address Line 2], bd.[Address Line 3], bd.[Address Line 4], bd.[Post Town], bd.County, bd.Postcode, bd.Country, bd.[Invoice No], bd.Invtot, bd.VehicleRegNo, bd.Plant, bd.[Created on], bd.CarManufacturer, bd.[Car model], bd.CarManufactYear, bd.Mileage, bd.Customer, bd.[branch code], bd.[urn no], bd.success, bd.[branch type], bd.[Month], bd.Marked, bd.DQFlag, bd.DQLinkID, md.DQID, md.ID, md.Salutation, md.[First Name], md.[Middle Name], md.Surname, md.[Full Name], md.Title, md.[Phone number], md.Email, md.[Web Address], md.Organisation, md.[Address Line 1], md.[Address Line 2], md.[Address Line 3], md.[Address Line 4], md.[Post Town], md.County, md.Postcode, md.Country, md.[Invoice No], md.Invtot, md.VehicleRegNo, md.Plant, md.[Created on], md.CarManufacturer, md.[Car model], md.CarManufactYear, md.Mileage, md.Customer, md.[branch code], md.[urn no], md.success, md.[branch type], md.[month], md.Marked, md.DQFlag, md.DQLinkID

    FROM

    [Branch data 2009 - 2012] AS bd INNER JOIN [main mailing data] AS md ON bd.dqlinkid = md.dqlinkid

    GROUP

    BY bd.DQID, bd.ID, bd.Salutation, bd.[First Name], bd.[Middle Name], bd.Surname, bd.[Full Name], bd.Title, bd.[Phone number], bd.Email, bd.[Web Address], bd.Organisation, bd.[Address Line 1], bd.[Address Line 2], bd.[Address Line 3], bd.[Address Line 4], bd.[Post Town], bd.County, bd.Postcode, bd.Country, bd.[Invoice No], bd.Invtot, bd.VehicleRegNo, bd.Plant, bd.[Created on], bd.CarManufacturer, bd.[Car model], bd.CarManufactYear, bd.Mileage, bd.Customer, bd.[branch code], bd.[urn no], bd.success, bd.[branch type], bd.[Month], bd.Marked, bd.DQFlag, bd.DQLinkID, md.DQID, md.ID, md.Salutation, md.[First Name], md.[Middle Name], md.Surname, md.[Full Name], md.Title, md.[Phone number], md.Email, md.[Web Address], md.Organisation, md.[Address Line 1], md.[Address Line 2], md.[Address Line 3], md.[Address Line 4], md.[Post Town], md.County, md.Postcode, md.Country, md.[Invoice No], md.Invtot, md.VehicleRegNo, md.Plant, md.[Created on], md.CarManufacturer, md.[Car model], md.CarManufactYear, md.Mileage, md.Customer, md.[branch code], md.[urn no], md.success, md.[branch type], md.[month], md.Marked, md.DQFlag, md.DQLinkID

    HAVING

    Count(bd.dqlinkid) = 2 OR Count(bd.dqlinkid) = 3

    Thursday, March 29, 2012 8:31 AM