Help with an SQL statement

I am adding a column to my fact table to count the number of occurrences in another field. For example, I will enter a '1' in my new field named 'Count' whenever the 'Type' field has 'Meal'. What I am doing is counting the number of meals in the system so I can add up all the costs and divide by the count to get average cost per meal.

The problem is that I don't want to count every line item. I only want to input a '1' in my 'Count' field for each meal per day. I am unsure how to do this. Here is what I have:

case when T1."Type" = 'Meal' then '1' else null end

I tried using "and max(T1."Date")" but it was not working.

Please help me develop this SQL so that I only have one count per day.

Thanks.I should give some more info to make the analysis easier...

My raw data looks like this

Date Type
20040101 Meal
20040101 Meal
20040101 Meal

I want the SQL to add a Count column and the Fact table data should look like this (one count per day).

Date Type Count
20040101 Meal 1
20040101 Meal 0
20040101 Meal 0

The only way I have been able to come close is to have a count of '1' for each line item, or to have to sum all the meals and have only one line per day. Is this possible to create without having to sum?

Thanks. Sorry I wasn't more clear.|||I still don't understand what you are trying to do. To get a count of meals, why don't you just
SELECT COUNT(*) From Table WHERE Type = 'Meal'

Do you want to count the number of days that have one meal so that if a single day has multiple meals, it just counts as one:
SELECT COUNT(*) From Table WHERE Type = 'Meal' GROUP BY datepart(year, Date), datepart(dayofyear, Date)|||Yes, I want to count one meal per day, however I still want the other line items to show as zero, because they have costs.

I'll try your SQL, but won't it drop the other 2 line items?

Thanks.|||select date expression
, count(*) as numberofmeals
, sum(costs)/count(*) as averagecostofmeal
from yourtable
group by date expression|||another solution :
select Date, Type into ##temp from yourtable group by Date, Type;
select Type,count(type) from ##temp group by type;

Help with an SQL statement

I am adding a column to my fact table to count the number of occurrences in another field. For example, I will enter a '1' in my new field named 'Count' whenever the 'Type' field has 'Meal'. What I am doing is counting the number of meals in the system so I can add up all the costs and divide by the count to get average cost per meal.

The problem is that I don't want to count every line item. I only want to input a '1' in my 'Count' field for each meal per day. I am unsure how to do this. Here is what I have:

case when T1."Type" = 'Meal' then '1' else null end

I tried using "and max(T1."Date")" but it was not working.

Please help me develop this SQL so that I only have one count per day.

Thanks.This is tough to help you do. The problem is that we (or I at least) don't understand your schema, so I'm not sure what you've got stored how.

One thing that I'd strongly suggest is to avoid using reserved words like "Count". You can use them, but it makes everything more work. In this case, I'd suggest using meal_count because it avoids the collision with a reserved word and it is more meaningful to some poor bozo like me that might try to help you!

-PatP|||This does not help. I was only giving you an example, I am using the 'Count' for the column title only, and am returning the same rows if I change the column title. I want to edit the SQL so I only count the number of days they had a meal charged.

Thanks.|||I'm sorry, I was trying to explain that I didn't know enough about your problem to help. If you can post the CREATE TABLE statements for your tables, and the SELECT statement (all of it), then I could get a lot closer. As it is, I don't know enough to give you anything more than guesses.

-PatP

Help with adding column

hi guys! I have a table with 3 columns but i realized that i need to add 1 column between column 2 and 3. Can anybody please help me on how to do that? Thanks in advance!just create a new table and populate it like this:

insert into NewTable
select col1, col2, 'some value', col3 from OldTable

then you can drop your old table. be sure to create indexes, fks, etc on the new table as appropriate once it's populated.|||i need to add 1 column between column 2 and 3.

The physical order of data in a database has no meaning.

Just use alter table and add the field to the end.
You can always specify the display in your select statements.

Help Running out of disk space.. filegroup question

Thanks so much Paul.
I'll try adding the second file to the primary filegroup.
Just one question about the proportional fill manner
(relative to the %occupied space. If drive E:/ has 471MBs
free and Drive F:\ (which will hold the new file) has 20
GBs free, will Drive E: still fill up and stop the
database or will the data just flow to the F:/ drive?
of the 2 files). Thanks.
Don

>--Original Message--
>Don,
>yes, you can add the new file to the existing filegroup.
>You won't be able to move objects to this file unless you
>create a new filegroup and add the new file to it,
however
>in your case this is probably not required as when new
>space is required in the database, it is added in
>proportional fill manner (relative to the %occupied space
>of the 2 files).
>HTH,
>Paul Ibison
>.
>
IIRC, data will flow to the new empty file before the old file will expand
beyond its current size. Once they both have the same free space
(relative), I'm not sure how SQL Server will decide which one to grow. To
prolong the wait, just make sure the new file is bigger than the old one.
;-)
http://www.aspfaq.com/
(Reverse address to reply.)
<anonymous@.discussions.microsoft.com> wrote in message
news:1ac8701c44f03$2b1996d0$a101280a@.phx.gbl...[vbcol=seagreen]
> Thanks so much Paul.
> I'll try adding the second file to the primary filegroup.
> Just one question about the proportional fill manner
> (relative to the %occupied space. If drive E:/ has 471MBs
> free and Drive F:\ (which will hold the new file) has 20
> GBs free, will Drive E: still fill up and stop the
> database or will the data just flow to the F:/ drive?
> of the 2 files). Thanks.
> Don
> however
|||Thanks Paul and Aaron,
I added the second primary group file to the F:/ Drive and
it looks like the data is going to there much faster than
to the E:/ drive that was filling up. It may be too early
to tell the exact ratio of E to F of data flow. It looks
like this fix saved the day.
Don
>--Original Message--
>IIRC, data will flow to the new empty file before the old
file will expand
>beyond its current size. Once they both have the same
free space
>(relative), I'm not sure how SQL Server will decide which
one to grow. To
>prolong the wait, just make sure the new file is bigger
than the old one.[vbcol=seagreen]
>;-)
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:1ac8701c44f03$2b1996d0$a101280a@.phx.gbl...
filegroup.[vbcol=seagreen]
471MBs[vbcol=seagreen]
filegroup.[vbcol=seagreen]
you[vbcol=seagreen]
space
>
>.
>
|||To prevent any further SQL initiated filegrowth on your E: drive you can disable automatic filegrow on the primary datafile
for this particular database all together and just allow the newly created data file on your other partition to grow, and/or add other data files as need arises. This will prevent SQL from filling up the precious remaining disk space (maybe you are alread
y using more than 90%) on your E: drive before the system halts running out of diskspace. I occasionally have to do this to balance disk load on terabyte size dbs.
|||Hi there,
One of our drives is completely filled up with a database file and so I would like to move some of the db objects to a newly created data file on another drive. Can anyone give me a hint on how to do this?
Thanks
Andreas
"Sassan Karai" wrote:

> To prevent any further SQL initiated filegrowth on your E: drive you can disable automatic filegrow on the primary datafile
> for this particular database all together and just allow the newly created data file on your other partition to grow, and/or add other data files as need arises. This will prevent SQL from filling up the precious remaining disk space (maybe you are alre
ady using more than 90%) on your E: drive before the system halts running out of diskspace. I occasionally have to do this to balance disk load on terabyte size dbs.
>
|||Hi
http://support.microsoft.com/directo...Q224071--
Moving SQL Server Databases to a New Location
"Andreas" <Andreas@.discussions.microsoft.com> wrote in message
news:58B7A052-6AAB-4E31-AD6F-A13F29A895CF@.microsoft.com...
> Hi there,
> One of our drives is completely filled up with a database file and so I
would like to move some of the db objects to a newly created data file on
another drive. Can anyone give me a hint on how to do this?[vbcol=seagreen]
> Thanks
> Andreas
>
> "Sassan Karai" wrote:
disable automatic filegrow on the primary datafile[vbcol=seagreen]
created data file on your other partition to grow, and/or add other data
files as need arises. This will prevent SQL from filling up the precious
remaining disk space (maybe you are already using more than 90%) on your E:
drive before the system halts running out of diskspace. I occasionally have
to do this to balance disk load on terabyte size dbs.[vbcol=seagreen]

Help Running out of disk space.. filegroup question

Thanks so much Paul.
I'll try adding the second file to the primary filegroup.
Just one question about the proportional fill manner
(relative to the %occupied space. If drive E:/ has 471MBs
free and Drive F:\ (which will hold the new file) has 20
GBs free, will Drive E: still fill up and stop the
database or will the data just flow to the F:/ drive?
of the 2 files). Thanks.
Don

>--Original Message--
>Don,
>yes, you can add the new file to the existing filegroup.
>You won't be able to move objects to this file unless you
>create a new filegroup and add the new file to it,
however
>in your case this is probably not required as when new
>space is required in the database, it is added in
>proportional fill manner (relative to the %occupied space
>of the 2 files).
>HTH,
>Paul Ibison
>.
>IIRC, data will flow to the new empty file before the old file will expand
beyond its current size. Once they both have the same free space
(relative), I'm not sure how SQL Server will decide which one to grow. To
prolong the wait, just make sure the new file is bigger than the old one.
;-)
http://www.aspfaq.com/
(Reverse address to reply.)
<anonymous@.discussions.microsoft.com> wrote in message
news:1ac8701c44f03$2b1996d0$a101280a@.phx
.gbl...[vbcol=seagreen]
> Thanks so much Paul.
> I'll try adding the second file to the primary filegroup.
> Just one question about the proportional fill manner
> (relative to the %occupied space. If drive E:/ has 471MBs
> free and Drive F:\ (which will hold the new file) has 20
> GBs free, will Drive E: still fill up and stop the
> database or will the data just flow to the F:/ drive?
> of the 2 files). Thanks.
> Don
>
> however|||Thanks Paul and Aaron,
I added the second primary group file to the F:/ Drive and
it looks like the data is going to there much faster than
to the E:/ drive that was filling up. It may be too early
to tell the exact ratio of E to F of data flow. It looks
like this fix saved the day.
Don
>--Original Message--
>IIRC, data will flow to the new empty file before the old
file will expand
>beyond its current size. Once they both have the same
free space
>(relative), I'm not sure how SQL Server will decide which
one to grow. To
>prolong the wait, just make sure the new file is bigger
than the old one.
>;-)
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
><anonymous@.discussions.microsoft.com> wrote in message
> news:1ac8701c44f03$2b1996d0$a101280a@.phx
.gbl...
filegroup.[vbcol=seagreen]
471MBs[vbcol=seagreen]
filegroup.[vbcol=seagreen]
you[vbcol=seagreen]
space[vbcol=seagreen]
>
>.
>|||To prevent any further SQL initiated filegrowth on your E: drive you can dis
able automatic filegrow on the primary datafile
for this particular database all together and just allow the newly created d
ata file on your other partition to grow, and/or add other data files as nee
d arises. This will prevent SQL from filling up the precious remaining disk
space (maybe you are alread
y using more than 90%) on your E: drive before the system halts running out
of diskspace. I occasionally have to do this to balance disk load on terabyt
e size dbs.|||Hi there,
One of our drives is completely filled up with a database file and so I woul
d like to move some of the db objects to a newly created data file on anothe
r drive. Can anyone give me a hint on how to do this?
Thanks
Andreas
"Sassan Karai" wrote:

> To prevent any further SQL initiated filegrowth on your E: drive you can d
isable automatic filegrow on the primary datafile
> for this particular database all together and just allow the newly created data fi
le on your other partition to grow, and/or add other data files as need arises. This
will prevent SQL from filling up the precious remaining disk space (maybe you are a
lre
ady using more than 90%) on your E: drive before the system halts running out of diskspace.
I occasionally have to do this to balance disk load on terabyte size dbs.
>|||Hi
[url]http://support.microsoft.com/directory/article.asp?ID=kb;en-us;Q224071--[/url
]
Moving SQL Server Databases to a New Location
"Andreas" <Andreas@.discussions.microsoft.com> wrote in message
news:58B7A052-6AAB-4E31-AD6F-A13F29A895CF@.microsoft.com...
> Hi there,
> One of our drives is completely filled up with a database file and so I
would like to move some of the db objects to a newly created data file on
another drive. Can anyone give me a hint on how to do this?[vbcol=seagreen]
> Thanks
> Andreas
>
> "Sassan Karai" wrote:
>
disable automatic filegrow on the primary datafile[vbcol=seagreen]
created data file on your other partition to grow, and/or add other data
files as need arises. This will prevent SQL from filling up the precious
remaining disk space (maybe you are already using more than 90%) on your E:
drive before the system halts running out of diskspace. I occasionally have
to do this to balance disk load on terabyte size dbs.[vbcol=seagreen]