Using COUNT() for a single table column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Using COUNT() for a single table column

Author

Topic

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-10-18 : 17:23:28

Hi,

I have a table which have many columns. There is a column named Active which is a bit datatype and this can be 1 or 0. Now some of the records are 1 and some of the records are 0. I want to find out how many records are 1 and how many are 0 using the COUNT function.

What I am doing is I am making a UNION between two queries but then the column name is the same so it is a problem. I am hoping that there should be a much simpler way of doing the same thing.

Thanks,

Mohammad Azam
www.azamsharp.net

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-18 : 17:28:48

Use a GROUP BY like this

SELECT Active, count(*)
FROM YourTable
GROUP BY Active

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-10-18 : 17:34:39

Hi,

The technique you described works well but the only problem is that I have other columns in the table like subject, body. When I use GROUP By then I have to use GROUP By with all the columns which results in the total chaos :(.

Like my query is something like:

SELECT Active, COUNT(*), Subject
FROM MyTable

GROUP BY Active, Subject // The subject messes everything up !

Mohammad Azam
www.azamsharp.net

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-18 : 17:40:30

In that case you're trying to do much more than you stated in your original question. Post a full description of what you're trying to do.

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-10-18 : 17:44:45

Thanks for the reply!

This is what I need:

I have a table MyTable which has the following fields:

MyTableID (Primary Key)
Subject (varchar(200))
Body (text)
Active (bit)

Now, I need to write a query to get the following:

MyTableID Subject Body [No Of Active Users] [De-Active Users]
1 aaa aaa 23 2
2 www www 34 12
3 eee eee 45 8

No of active users is where Active = 1
And De-Active Users is where Active = 0

hope this helpS!

Mohammad Azam
www.azamsharp.net

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-18 : 18:14:49

That doesn't make sense I'm afraid because you can only have one row for each primary key value, so unless you have another table and the Active column is in the other table, every row is either going to be 1 for active and 0 for inactive, or vice versa.

Alternatively, you don't want to show the MyTableId in the query and you have lots of Subject and Body values that are equal? Even if that is the case this won't work because you can't group on a text column (you can force it if you really want to but it wouldn't make much sense to do so).

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-10-18 : 19:58:43

Thanks! I guess I have to do this in the business logic.

Thanks for your help!

Mohammad Azam
www.azamsharp.net

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-18 : 21:08:19

No - you can easily write a query to do what you want, but you haven't given enough info about your data for me to be able to help you with the query. Are there two tables, and if so what is the structure of the other one etc.?

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-10-19 : 11:40:15

Sorry for the late reply.

Here is my table schema:

CREATE TABLE [dbo].[tblPrivateMessageUsers] (
[PrivateMessageUserID] [int] IDENTITY (1, 1) NOT NULL ,
[From_UserLoginID] [int] NOT NULL ,
[To_UserLoginID] [int] NOT NULL ,
[PrivateMessageID] [int] NOT NULL ,
[ReceiverTypeID] [int] NOT NULL ,
[SectionID] [int] NOT NULL ,
[Viewed] [bit] NOT NULL ,
[Active] [bit] NOT NULL ,
[CTDS] [datetime] NOT NULL ,
[MTDS] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblPrivateMessages] (
[PrivateMessageID] [int] IDENTITY (1, 1) NOT NULL ,
[MessageSubject] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
[MessageBody] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
[Active] [bit] NOT NULL ,
[CTDS] [datetime] NOT NULL ,
[MTDS] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

The tblPrivateMessages stored the messages information. The table tblPrivateMessageUsers stored the information that who sent the mesage and to whom.

So the input paramaters to the T-Sql will be the userLoginID and I will retreieve the following infomration:

PrivateMessageID Subject Body [Total Users Viewed Message] [Total Users Not Viewed Message]

The final dispaly will be something like this:

1 This is subject This is body 23 45
2 This is new subject This is new body 34 56

I hope you get the idea!
Thanks,
Azam

Mohammad Azam
www.azamsharp.net

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-19 : 12:20:21

Now it sounds like you want the query to return information based on the Viewed column in tblPrivateMessageUsers, but you haven't really been very clear about that - can you see that I'm trying to help but you are not helping me to help you?

Anyhow, here is what I guess you want:


SELECT v.PrivateMessageID, m.MessageSubject, m.MessageBody
    , v.[Total Users Viewed Message]
    , v.[Total Users Not Viewed Message] 
FROM
(SELECT PrivateMessageID
    , SUM(case Viewed when 1 then 1 else 0 end) AS [Total Users Viewed Message]
    , SUM(case Viewed when 1 then 0 else 1 end) AS [Total Users Not Viewed Message]
FROM tblPrivateMessageUsers
WHERE To_UserLoginID = @userLoginId
GROUP BY PrivateMessageID) v
INNER JOIN tblPrivateMessages m ON m.PrivateMessageID = v.PrivateMessageID

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-10-19 : 12:30:37

Hi,

Thanks for the help! I am apololize for my communication. Your query returns the result like the following:

PrivateMessageID MessageSubject MessageBody Total Users Viewed Mesage Total USers Not Viewed Message
23 aaaa ssss 0 1
34 hhh sss 1 0

I think you are really close since I need the number of users who viewed the message and not viewed the message. Something like this:

PrivateMessageID MessageSubject MessageBody Total Users Viewed Mesage Total USers Not Viewed Message
23 aaaa ssss 23 12
34 hhh sss 45 23

I hope you get the idea.

Mohammad Azam
www.azamsharp.net

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-10-19 : 12:38:29

Ohh got it. I was using the wrong userLoginID.

Thanks for all the help!!! I really appreciate it.
Thanks,
Azam

Mohammad Azam
www.azamsharp.net

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources