August 23, 2010 at 2:58 pm
Hi I am writing this tsql but I don't know how to add COUNT value in the table.
Any idea please?
What I am doing is count all the total by PickupID from @Deli and write the value to @DeliTotal Table.
And also Count all the "Sign" Status by PickupID and update to the TotalSign column.
I tried in MS Access & worked with one query. But I think DCount aggregate function doesn't work in TSQL.
Thanks
DECLARE @Deli TABLE (PickupID int, Status Text)
DECLARE @DeliTotal TABLE (PickUpID INT,
Total Int,
TotalSign int)
Insert into @Deli values(1, 'Sign')
Insert into @Deli values(1, 'Sign')
Insert into @Deli values(2, 'Dlvy')
Insert into @Deli values(2, 'Sign')
Insert into @Deli values(3, 'Sign')
Insert into @Deli values(1, 'Dlvy')
INSERT INTO @DeliTotal (PickupID, Total)
SELECT PickupID, count(PickupID) as CntPick
FROM @Deli
Group By PickupID
UPDATE @DeliTotal SET TotalSign = COUNT(DISTINCT PickupID)
FROM @DeliTotal a inner join @Deli b on a.PickupID = b.PickupID
Where Status = 'Sign'
I am also trying this code
DECLARE @Deli TABLE (PickupID int, Status Text)
DECLARE @DeliTotal TABLE (PickupID INT,
Total Int,
TotalSign int)
Insert into @Deli values(1, 'Sign')
Insert into @Deli values(1, 'Sign')
Insert into @Deli values(2, 'Dlvy')
Insert into @Deli values(2, 'Sign')
Insert into @Deli values(3, 'Sign')
Insert into @Deli values(1, 'Dlvy')
INSERT INTO @DeliTotal
(b.PickupID, b.Total, b.TotalSign)
SELECT a.PickupID,
count(a.PickupID) as CntPick,
(Select count(*) from @Deli Where Status = 'Sign' and a.PickupID = b.PickupID)
FROM @Deli a inner join @DeliTotal b on a.Pickupid = b.Pickupid
The code replied error
Msg 8120, Level 16, State 1, Line 14
Column '@Deli.PickupID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
August 23, 2010 at 3:14 pm
Hi,
Finally I got it. I am little bit dump and confused to write a code as a beginner...:-D
Here is the code. and correct me if I am wrong.
DECLARE @Deli TABLE (PickupID int, Status varchar(4))
DECLARE @DeliTotal TABLE (PickupID INT,
Total Int,
TotalSign int)
Insert into @Deli values(1, 'Sign')
Insert into @Deli values(1, 'Sign')
Insert into @Deli values(2, 'Dlvy')
Insert into @Deli values(2, 'Sign')
Insert into @Deli values(3, 'Dlvy')
Insert into @Deli values(1, 'Dlvy')
INSERT INTO @DeliTotal
(PickupID, Total, TotalSign)
SELECT PickupID,
count(PickupID) as CntPick,
(select count(*) from @Deli b Where b.PickupID = a.PickupID and Status = 'Sign')
FROM @Deli a
group by pickupid
select * from @DeliTotal
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply