Count query problem :(

Hi all,

Hope someone can help me with this ...

This is my query. The problem with it is that it only returns Areas with >0
Topics. So if a new Area is created, it hasn't a topic until one is
created, and it doesn't list!

Is it possible to return even those rows where the count is 0, or what is
another way around this?

Thanks in advance, Jen

SELECT dbo.chat_board.boardID, dbo.chat_area.areaID,
dbo.chat_area.name, dbo.chat_area.datecreated,
COUNT(dbo.chat_topic.topicID) AS counttopics
FROM dbo.chat_board INNER JOIN
dbo.chat_area ON
dbo.chat_board.boardID = dbo.chat_area.boardID INNER JOIN
dbo.chat_topic ON
dbo.chat_area.areaID = dbo.chat_topic.areaID
GROUP BY dbo.chat_board.boardID, dbo.chat_area.areaID,
dbo.chat_area.name, dbo.chat_area.datecreated

--
Fast Track On Line -Web Design and Development
Portfolio http://www.fasttrackonline.co.uk

--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004On Wed, 2 Jun 2004 11:55:27 +0100, Jenny wrote:

>Hi all,
>Hope someone can help me with this ...
>This is my query. The problem with it is that it only returns Areas with >0
>Topics. So if a new Area is created, it hasn't a topic until one is
>created, and it doesn't list!
>Is it possible to return even those rows where the count is 0, or what is
>another way around this?
>Thanks in advance, Jen
>SELECT dbo.chat_board.boardID, dbo.chat_area.areaID,
> dbo.chat_area.name, dbo.chat_area.datecreated,
> COUNT(dbo.chat_topic.topicID) AS counttopics
>FROM dbo.chat_board INNER JOIN
> dbo.chat_area ON
> dbo.chat_board.boardID = dbo.chat_area.boardID INNER JOIN
> dbo.chat_topic ON
> dbo.chat_area.areaID = dbo.chat_topic.areaID
>GROUP BY dbo.chat_board.boardID, dbo.chat_area.areaID,
> dbo.chat_area.name, dbo.chat_area.datecreated

Hi Jenny,

Try replacing the second "INNER JOIN" with "LEFT OUTER JOIN". Check Books
Online for the details.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

count problem

Hi.
I have a table containing two groups and a detail area.
I want to add a counter near the second group area.
It should start from 1 and increment by one as second group value changes.(1, 2, 3, ...)
I tried to use count, countRows .... but they didn't work.
I don't want to learn total count of group value.
How can I do this?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=489400&SiteID=1

Take a look at this post and see if this can help you out! You may have to make some changes, but this might be a start.

If you think this could help, and are unsure about how you could get it to work I brainstormed along this line...

This assumes that you will be doing all your orderring and selection criteria in your SQL statements.

Select your group into a temporary table counting the "detail" rows (this will be your groups). Then join the temp table into your dataset. If all is orderred properly, you should get a dataset that contains your records and your group number in a column.

Like I said, this is just a brain storm. I believe it would work, I just haven't tried it. I do know that the post I've linked to contains some of my code that I tested and verified to work. I have had others tell me it works for them as well. Let me know what you think, and if you need me to expand upon my brainstorm.

Good Luck!

Curtis

|||I have looked at this link lots of times but it didn't work.
You wrote about temp table method.
How can I create a temp table and how can I use it?|||

I'm working on something similiar right now. I'll have a description and a sample before long, unless someone else beats me to the punch.

Curtis

|||

In the group header, you can use a running value based on the CountDistinct function:

=RunningValue(Fields!Id.Value, CountDistinct, Nothing)

Explanation:
The first argument in the running value should be identical to your grouping expression.
The second argument specifies that the CountDistinct function should be used during the calculation.
The third argument specifies that the running value should never reset in your table. If you want it to reset e.g. based on the outer group scope, then specify the outer group name as "reset"-scope.

-- Robert

|||

RunningValue(Fields!Id.Value, CountDistinct, Nothing)

I tried using this. My situation is, I have groups with sub-groups. Some of my sub-groups are not distinct. So something like this may work if every group is distinct, but how do we take in account non-distinct groups? Changing CountDistinct to Count counts all the detail rows, and that's not what I want. I just want a running count of ALL my sub-groups.

This is why I am going the route that I brain stormed. Unless there is an easier way to do it?

Thanks

Curtis

|||

In that case, you will need to look into the custom aggregation approach, similar to the sample in this blog article: http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx

-- Robert

|||

Robert Bruckner MSFT wrote:

In the group header, you can use a running value based on the CountDistinct function:

=RunningValue(Fields!Id.Value, CountDistinct, Nothing)

Thanks Robert!
It worked successfully.