#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2016
    Location
    Selm, North Rhine-Westphalia, Germany
    Posts
    1
    Rep Power
    0

    Problems with this SQL statement


    Hi,

    i use firebird embedded local and i am developing with delphi using firedac to connect to the db.

    i have a table called AnalyseResultHTagDetailTable with the columns HTAGTEXT,HTAGURL,HTAGSIZE,MIMETYPE,HTTPSTATUS

    now i want to select all duplicate items in the column HTAGTEXT and display them grouped so i wanted to use this sql statement:

    Code:
    'select HTAGTEXT,HTAGURL,HTAGSIZE,MIMETYPE,HTTPSTATUS, COUNT(HTAGTEXT) AS NumOccurrences from AnalyseResultHTagDetailTable GROUP BY HTAGTEXT HAVING ( COUNT(HTAGTEXT) > 1 );
    When using it i get an 104 SQL error. The problem for me is that that one here works

    Code:
    'select HTAGTEXT, COUNT(HTAGTEXT) AS NumOccurrences from AnalyseResultHTagDetailTable GROUP BY HTAGTEXT HAVING ( COUNT(HTAGTEXT) > 1 );
    but then i cannot display the rest of the column data in the results. Can anybody help me with this problem please.

    Best regards and greets from germany,

    Andy
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,402
    Rep Power
    394
    Code:
    select analyse.HTAGTEXT,
           analyse.HTAGURL,
           analyse.HTAGSIZE,
           analyse.MIMETYPE,
           analyse.HTTPSTATUS,
           dt.numberOfOccurrences
      from AnalyseResultHTagDetailTable analyse
      join (select HTAGTEXT,
                   count(*) as numberOfOccurrences
              from AnalyseResultHTagDetailTable
            group
               by HTAGTEXT
           having count(*) > 1) dt
        on analyse.HTAGTEXT = dt.HTAGTEXT

    Comments on this post

    • ASoraru agrees
    Last edited by swampBoogie; April 1st, 2016 at 11:43 AM. Reason: Typo in formatting option
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2008
    Posts
    74
    Rep Power
    10
    try studying use "CET" - Common Expression Table
    very quickly and you can found very examples in the net include here in forum

    this tech its very advanced to hard expression on SQL result

    see firebirdsql
    org faqs / forum

IMN logo majestic logo threadwatch logo seochat tools logo