SQL Count function inside an insert command

Question

I want to set the value of one of the fields with a number that is derived from a count function. But How do I do that?

    StringBuilder insertCommand = new StringBuilder();
 insertCommand.Append("INSERT INTO Threads(UsersID,TopicsID,Date,ThreadTitle,ThreadParagraph,ThreadClosed,Views,Replies,PageNumber)");
    insertCommand.Append("VALUES(@uniqueIdentifier,@TopicsID,GETDATE(),@questionTitle,@questionParagraph,0,0,0,@pageNumber)");

To set the page number parameter i want to do something like this:

    sqlCommand.Parameters.Add("@subTopic", SqlDbType.Int);
    sqlCommand.Parameters["@subTopic"].Value = "Count(ThreadID)/20";

I want to input a number that is divisible by 20 into PageNumber field in table Threads. Note: The number should be a whole number.. so instead of returning 10/20=0.5, it should return 0.

Blindy · Accepted Answer · 2011-06-07 15:52:49Z

1

You can use a query insert, like this:

INSERT INTO Threads (UsersID,TopicsID,Date,ThreadTitle,ThreadParagraph,
  ThreadClosed,Views,Replies,PageNumber)
SELECT @uniqueIdentifier,@TopicsID,GETDATE(),@questionTitle,
  @questionParagraph,0,0,0,FLOOR(Count(ThreadID)/20)
FROM table

Assuming the subtopic parameter name was a typo and you actually meant pagenumber. You never know though!

answered Jun 7, 2011 at 15:52

Blindy

68k10 gold badges96 silver badges141 bronze badges

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

3 Comments

Blindy Over a year ago

Well you said you want 0 instead of 0.5. That's what FLOOR does.

Matrix001 Over a year ago

The problem now is that it doesnt recoginize the ThreadID , which is a primary key, and increments. Thats because threadID is a uniqueIdentifier. I set the whole thing to TopicID. FLOOR(Count(@TopicsID)... but it gives me 1. (TopicID is an auto incremental int, in the Topic table that is connected to ThreadsTable. They both increase together...Why i dont get the real count of TopicID. It is 6, but when i extract it it is 1. :(

Matrix001 Over a year ago

I corrected the mistake. I simply used inner join, to get the TopicsID in the Topics table. Thanks for the help

Collectives™ on Stack Overflow

SQL Count function inside an insert command

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related