Insert Into With Count()

Question

Insert Into With Count()

Rixxe

Ten Centuries

Points: 1104
More actions
December 7, 2011 at 4:11 am

#264190

Hi All,
I've created a table like this:
Create table OutputTable (PatientCount int , AgeRange nvarchar (40), Method nvarchar (20)
, Sex nvarchar (2), AvAge numeric, MaxAge numeric, MinAge numeric)
Then i'm attempting to insert data into it using this query:
Insert into OutputTable(PatientCount, AgeRange, Method, Sex, AvAge, MaxAge, MinAge)select Count(patid),
'Age 100-109', 'Method1', sex ,averageage, maximumage, minimumage from INCPatient1_1 where sex = '1'
and (age between '100' and '109')
group by sex, averageage, maximumage, minimumage
Now for this particular query the count will be 0, as there is no one that fullfills this age range, however when running this, the table doesnt update at all. Just states '0 row(s) affected'.
I'd like to add in a '0' to the Count if possible, to get the other information.
I've tried :
ISNULL()
Setting the table to not allow nulls/allow nulls
These seem to be the only answers that people use, but it doesn't change the result in this particular case.
Any ideas?
Many thanks
R
Using SQLServerExpress if that makes a difference.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

INSERT...SELECT will insert rows from the output of the SELECT. If there are no rows returned by the SELECT, then no rows will be inserted.

In your case, you want to construct a dummy row if the SELECT returns no rows. Forget about the INSERT part for now, and focus on constructing a SELECT which will always return either real rows, or if there are none, then a dummy row instead.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 2

What value would you like to insert for the other columns like sex, averageage, maximumage and minimumage?

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Rixxe Ten Centuries Points: 1104 More actions · Answer 3

ChrisM@Work (12/7/2011)
INSERT...SELECT will insert rows from the output of the SELECT. If there are no rows returned by the SELECT, then no rows will be inserted.
In your case, you want to construct a dummy row if the SELECT returns no rows. Forget about the INSERT part for now, and focus on constructing a SELECT which will always return either real rows, or if there are none, then a dummy row instead.

That makes sense. Would you recommend using an IF...ELSE statement if there are no rows returned?

Many thanks

R

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Let me ask another question. This looks like a single step in the creation of a complex cross-tabbed report. Is this the case?

If so, is the data you're inserting into the table used for anything else?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Rixxe Ten Centuries Points: 1104 More actions · Answer 5

ChrisM@Work (12/7/2011)
Let me ask another question. This looks like a single step in the creation of a complex cross-tabbed report. Is this the case?
If so, is the data you're inserting into the table used for anything else?

Basically, these steps will be run on all age ranges for a number of different datasets, which will, at some point be combined to give a final count.

Main issue being, if there is no record (i.e. just nothing for that age range) the combining 'phase' won't work.

I'd rather not insert false records just to get a '0' for the count.

Overall the whole process could probably be implemented better, but that isn't up to me.

Another option i thought about would be to use 'Update' rather than insert, and just update with a zero if the count is null. But then, not sure how/if this would work.

Thanks

R

Divine Flame SSCoach Points: 15943 More actions · Answer 6

Rixxe (12/7/2011)
I'd rather not insert false records just to get a '0' for the count.

You shouldn't. You can always use IF EXISTS to know if there is any row available for the SELECT statement you have given.

Rixxe (12/7/2011)
Another option i thought about would be to use 'Update' rather than insert, and just update with a zero if the count is null. But then, not sure how/if this would work.

If this data is being prepared for the reports, you can very well get the count as 0 when you put it like below :

SELECT COUNT (PatientCount) AS PatientCount FROM OutputTable where sex = '1'

and AgeRange = 'Age 100-109'

In the above query, if there is no row with AgeRange = 'Age 100-109' then the count will be returned as 0 itself instead of NULL.

Sujeet Singh

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Rixxe (12/7/2011)
ChrisM@Work (12/7/2011)
Let me ask another question. This looks like a single step in the creation of a complex cross-tabbed report. Is this the case?
If so, is the data you're inserting into the table used for anything else?
Basically, these steps will be run on all age ranges for a number of different datasets, which will, at some point be combined to give a final count.
Main issue being, if there is no record (i.e. just nothing for that age range) the combining 'phase' won't work.
I'd rather not insert false records just to get a '0' for the count.
Overall the whole process could probably be implemented better, but that isn't up to me.
Another option i thought about would be to use 'Update' rather than insert, and just update with a zero if the count is null. But then, not sure how/if this would work.
Thanks
R

Have a wee play with this.

SELECT

Count(*),

ar.Rangename,

'Method1',

ip.sex,

averageage = AVG(ip.age),

ipmaximumage = MAX(ip.age),

minimumage = MIN(ip.age)

FROM (

SELECT Rangename = 'Age 100-109', Lowerbound = 100, Upperbound = 109 UNION ALL

SELECT Rangename = 'Age 90-99', Lowerbound = 90, Upperbound = 99 UNION ALL

SELECT Rangename = 'Age 80-89', Lowerbound = 80, Upperbound = 89

) ar

LEFT JOIN INCPatient1_1 ip ON ip.age between ar.Lowerbound AND ar.Upperbound

GROUP BY ar.Rangename, ip.sex

You will probably need another derived table holding whatever values your health authority uses for gender.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Rixxe Ten Centuries Points: 1104 More actions · Answer 8

Thanks, i'll give this a go and see what i can come up with.

Cheers

R

Rixxe Ten Centuries Points: 1104 More actions · Answer 9

Divine Flame (12/7/2011)
Rixxe (12/7/2011)
I'd rather not insert false records just to get a '0' for the count.
You shouldn't. You can always use IF EXISTS to know if there is any row available for the SELECT statement you have given.
Rixxe (12/7/2011)
Another option i thought about would be to use 'Update' rather than insert, and just update with a zero if the count is null. But then, not sure how/if this would work.
If this data is being prepared for the reports, you can very well get the count as 0 when you put it like below :
SELECT COUNT (PatientCount) AS PatientCount FROM OutputTable where sex = '1'
and AgeRange = 'Age 100-109'
In the above query, if there is no row with AgeRange = 'Age 100-109' then the count will be returned as 0 itself instead of NULL.

The issue is that i needed to insert these results into a table, and as stated by ChrisM@Work because the select query doesn't return any results, when trying to insert, it does nothing (doesn't even insert the 0).

Anyway,ChrisM@Work that query does work, and there was another table to join in order to get the gender so that wasn't a problem.

I also thought of a slightly less optimal way of getting around the problem:

Create a table and imput the column names, set all the counts to '0' (and any other fields that are needed). I've then ran a simple update query which just adds to the existing value.

As a small last question, with SQLserver express, during a WHILE Loop, any idea why the PRINT doesn't display? In C# when you write to console during a loop it does get displayed as the program runs, not just at the end. Any ideas?

Many thanks again.

R

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

In grown-up SQL Server folks often use

RAISERROR('Almost instant screen print',10,1) WITH NOWAIT;

rather than PRINT.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden