SQL COUNT function

Databases
1

I have a query that generates a list of website topics from our CMS database. In addition to what it pulls from the database, the page also shows page counts for each topic, and the counting is done in the code, not the SQL. It’s worked well for a number of years.

As time has passed, we’ve added a lot of content, so now I want to be able to sort by page count. So that means adding COUNT to the query.

The old query looked like this:

SELECT    pagetopics.topic_id
	, pagetopics.topic_name
        , pagetopics.contact_id
        , pagetopics.office
        , pagetopics.publish
        , pagecontacts.page_name
        , pagecontacts.page_title
        , pagecontacts.contact_id AS defaultcontact
        , maindir.main_id
        , maindir.first_name & ' ' & maindir.last_name AS fullname
FROM ( pagetopics
LEFT JOIN pagecontacts
	ON pagetopics.topic_id = pagecontacts.topic_id
        )
LEFT JOIN maindir
	ON maindir.main_id = pagetopics.contact_id

I’ve tried to tweak it like this:


SELECT    pagetopics.topic_id
	, pagetopics.topic_name
        , pagetopics.contact_id
        , pagetopics.office
        , pagetopics.publish
        , pagecontacts.page_name
        , pagecontacts.page_title
        , pagecontacts.contact_id AS defaultcontact
        , maindir.main_id
        , maindir.first_name & ' ' & maindir.last_name AS fullname
        , COUNT(pagecontacts.page_id) AS pages
FROM ( pagetopics
LEFT JOIN pagecontacts
	ON pagetopics.topic_id = pagecontacts.topic_id
        )
LEFT JOIN maindir
	ON maindir.main_id = pagetopics.contact_id
GROUP BY pagetopics.topic_id
	, pagetopics.topic_name
        , pagetopics.contact_id
        , pagetopics.office
        , pagetopics.publish
        , pagecontacts.page_name
        , pagecontacts.page_title
        , pagecontacts.contact_id
        , maindir.main_id
        , maindir.first_name & ' ' & maindir.last_name

And of course my count is 1 for every topic. I think I made to do a subquery, but I’m not having any epiphanies. Any advice?

2

could you briefly identify the 1-to-many relationships between those three tables

3

Yup!

pagetopics has a list of topics, each having a topic name and ID

pagecontacts is a list of actual pages, and there is a topic_id column.

Each page can have only one topic, but each topic can have as many pages as it wants.

maindir is our staff directory. Name, ID, etc.

Each topic in pagetopics has a default contact, so pagetopic and maindir are 1-1 on this case.

4

is this ms access? because you have parentheses in your FROM clause

try this –

SELECT pagetopics.topic_id
     , pagetopics.topic_name
     , pagetopics.contact_id
     , pagetopics.office
     , pagetopics.publish
     , pagecontacts.page_name
     , pagecontacts.page_title
     , pagecontacts.contact_id AS defaultcontact
     , maindir.main_id
     , maindir.first_name & ' ' & maindir.last_name AS fullname
     [COLOR="#FF0000"], ( SELECT COUNT(*)
           FROM pagecontacts
          WHERE topic_id = pagetopics.topic_id ) AS topics[/COLOR]
  FROM ( 
       pagetopics
LEFT 
  JOIN pagecontacts
    ON pagecontacts.topic_id = pagetopics.topic_id 
       )
LEFT 
  JOIN maindir
    ON maindir.main_id = pagetopics.contact_id
5

That’s almost exactly like a version I tried, but I did COUNT(page_id) rather than COUNT(*) so that’s probably what I did wrong.

Thanks! It works.

Now I have to figure out how to ORDER BY that count.

6

actually it was the fact that you didn’t use a subselect in the SELECT clause, but rather the COUNT() with a GROUP BY in the outer query

my query did not use a GROUP BY, but a correlated subquery instead

ORDER
    BY topics DESC

:slight_smile:

7

I did try a subselect, but I got an error, so I must have done something else wrong.

And I can’t order by like that, I get an error:

“No value given for one or more required parameters.”

I think I’m not allowed to order by a column alias.

8

yes you are

please show the exact query you tried it on

9

Here we go.

SELECT    pagetopics.topic_id
	, pagetopics.topic_name
        , pagetopics.contact_id
        , pagetopics.office
        , pagetopics.publish
        , pagecontacts.page_name
        , pagecontacts.page_title
        , pagecontacts.contact_id AS defaultcontact
        , maindir.main_id
        , maindir.first_name & ' ' & maindir.last_name AS fullname
        , ( SELECT COUNT(*)
            FROM pagecontacts
            WHERE topic_id = pagetopics.topic_id ) AS pages
FROM ( pagetopics
LEFT JOIN pagecontacts
	ON pagetopics.topic_id = pagecontacts.topic_id
        )
LEFT JOIN maindir
	ON maindir.main_id = pagetopics.contact_id
GROUP BY pagetopics.topic_id
	, pagetopics.topic_name
        , pagetopics.contact_id
        , pagetopics.office
        , pagetopics.publish
        , pagecontacts.page_name
        , pagecontacts.page_title
        , pagecontacts.contact_id
        , maindir.main_id
        , maindir.first_name & ' ' & maindir.last_name
ORDER BY pages desc
10

remove the GROUP BY clause in its entirety

11

UGH! I don’t need GROUP BY because the COUNT is in a subselect!

But I still get the same error. I wonder if it’s an Access thing (yes, despite SQL Server 2008 being available, we STILL have to use Access because they haven’t decided who will have access to create/modify databases).

12

try naming the column some other name, i’m guessing pages might be a reserved word?

13

I tried that (numpages) and got the same error.

14

maybe the error message is referencing some other part of the sql statement

15

I thought about that, but it works when I remove the ORDER BY and returns when I re-add it.

Google “sql order by column alias” returns quite a few results of people experiencing the same problem.

16

try like this –

SELECT *
  FROM ( SELECT ...
              , ( SELECT COUNT(*)
                    FROM ... ) AS pages
           FROM ... ) AS query
ORDER
    BY pages DESC
17

Oh my, that sure is thinking outside the box!

And it works!

And as a side perk, I got you to use the evil, dreaded SELECT * !!! :smiley:

18

as with any rule, there are exceptions

it is assumed that the nested query, which used to be your original query, lists all of the columns it wants to return, including the pages count

therefore, there is no uncertainty about which columns the outer nesting query will receive, and thus the use of the asterisk is good

:smiley:

19

Which is also probably why I haven’t noticed any performance differences between the new and old queries.

As always, thanks for the help rudy!