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 required to update SQL

I have a table named Car with the field name as Position. I want to
update the Position field. If i enter a new value as 4 for position
which already exist, then the existing value 4 and all the below items
like 5,6 and 7 must be incremented by 1
Position
1
2
3
4
5
6
7
How can this be achievedmora wrote:
> I have a table named Car with the field name as Position. I want to
> update the Position field. If i enter a new value as 4 for position
> which already exist, then the existing value 4 and all the below items
> like 5,6 and 7 must be incremented by 1
>
> Position
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> How can this be achieved
UPDATE Car SET Position = Position + 1 WHERE Position >= 4 ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||CREATE TRIGGER blahblah INSTEAD OF INSERT AS
BEGIN
declare @.pos int
select @.pos = inserted.position
UPDATE table1 SET position = position+1 WHERE position >= @.pos
INSERT INTO table1 SELECT * from inserted
END
-- please verify the syntax before using - I'm just giving an idea,
don't have a server/BOL around to test & debug it.|||>> I have a table named Car with the field [sic] name as position. I want to update th
e position field [sic]. <<
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Your vageu narrative is also wrong. Columns are not fields. POSITION()
s a reserved word in Standard SQL. Since you used a singular name, you
must have one car; otherwise you would have used a collective or plural
noun.
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" , "below" and "last" are
totally meaningless.
I am going to make a guess at what you meant. Do you have a motorpool
in which you assign parkging spaces?
CREATE TABLE Motorpool
(space_nbr INTEGER NOT NULL PRIMARY KEY
CHECK (space_nbr > 0),
vin CHAR(17) NOT NULL);
Re-arrange the display order based on the space_nbr column:
CREATE PROCEDURE SwapParkingSpacees (@.old_space_nbr INTEGER,
@.new_space_nbr INTEGER)
AS
UPDATE Motorpool
SET space_nbr
= CASE space_nbr
WHEN @.old_space_nbr
THEN @.new_space_nbr
ELSE space_nbr + SIGN(@.old_space_nbr - @.new_pos)
END
WHERE space_nbr BETWEEN @.old_space_nbr AND @.new_space_nbr
OR space_nbr BETWEEN @.new_space_nbr AND @.old_space_nbr;
When you want to drop a few rows, remember to close the gaps with this:
CREATE PROCEDURE CloseParkingSpaceGaps()
AS
UPDATE Motorpool
SET space_nbr
= (SELECT COUNT (F1.space_nbr)
FROM Motorpool AS F1
WHERE F1.space_nbr <= Motorpool.space_nbr);
To insert a new car into the motorpool, add the new vehicle to the "end
of the line" and then swap it with the target parking space.