Adding LIMIT 1 to COUNT query

Question

This is a COUNT query I'm using to display pages:

$sql = "
SELECT SUM(num) as num FROM (
  SELECT
    COUNT(URL) AS num
    , 'World' AS GoSection
    , 'GW' AS MySite
  FROM gw_geog
  WHERE URL = :MyURL AND G1 = 1
UNION ALL
  SELECT
    COUNT(URL) AS num
    , 'World' AS GoSection
    , 'GW' AS MySite
  FROM gw_geog_political
  WHERE URL = :MyURL
 ) AS X";

$stmt = $pdo->prepare($sql);
$stmt->bindParam(':MyURL',$MyURL,PDO::PARAM_STR);
$stmt->execute();
$Total = $stmt->fetch();

I want to make two changes:

1) Delete WHERE URL = :MyURL from each line and incorporate it into a single line at the end of the query

2) Zap duplicates by adding LIMIT 1

This is what I'm working with right now.

$sql = "SELECT SUM(num) as num FROM (
 SELECT COUNT(URL) AS num, 'World' AS GoSection, 'GW' AS MySite FROM gw_geog WHERE URL = :MyURL AND G1 = 1
 UNION ALL
 SELECT COUNT(URL) AS num, 'World' AS GoSection, 'GW' AS MySite FROM gw_geog_political WHERE URL = :MyURL
 ) AS X
WHERE X.URL LIKE :MyURL LIMIT 1";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':MyURL',$MyURL,PDO::PARAM_STR);
$stmt->execute();
$Total = $stmt->fetch();

However, I get the error message Unknown column X.SUM (or X.URL, etc.).

Can someone show me the correct way to do this?

can you clarify "zap duplicates"? to me that means it should only count each distinct URL once, regardless of which table it lives in, is that right? — I wrestled a bear once.
– I wrestled a bear once., Commented Oct 7, 2015 at 1:44

Dhvani Purohit · Accepted Answer · 2015-10-07 14:12:46Z

1

Use Query As Follows and you will get your desired output:

 SELECT
    SUM(num) AS num
FROM
(
    SELECT
        COUNT(URL) AS num,
        'World' AS GoSection,
        'GW' AS MySite,
        gw_geog.`URL` as URL  
    FROM
        gw_geog
    WHERE
        URL = :MyURL
    AND G1 = 1
    UNION ALL
        SELECT
            COUNT(URL) AS num,
            'World' AS GoSection,
            'GW' AS MySite,
             gw_geog_political.`URL` as URL
        FROM
            gw_geog_political
        WHERE
            URL = :MyURL
) AS X
WHERE
    X.URL like  :MyURL
LIMIT 1

answered Oct 7, 2015 at 14:12

Dhvani Purohit

364 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

I wrestled a bear once. · Accepted Answer · 2015-10-07 02:03:46Z

0

If I understand you, limit 1 is not what you're looking for.

Here's a fiddle: http://sqlfiddle.com/#!9/5ffbd/5

SELECT count(URL) AS num
    ,'World' AS GoSection
    ,'GW' AS MySite
FROM (
    SELECT URL
        ,G1
        ,'a' AS tbl
    FROM gw_geog

    UNION ALL

    SELECT URL
        ,G1
        ,'b' AS tbl
    FROM gw_geog
    ) a
WHERE a.tbl = 'b'
    OR (
        a.tbl = 'a'
        AND a.G1 = 1
        )
    AND a.URL = :MyURL

answered Oct 7, 2015 at 2:03

I wrestled a bear once.

23.5k20 gold badges74 silver badges122 bronze badges

Collectives™ on Stack Overflow

Adding LIMIT 1 to COUNT query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related