November 17, 2004 at 4:47 am
Hi People,
I hope you can help me with this one - I'm very new to SQL!
I have a table of items that have been despatched. This can boil down to 2 columns:
DESPATCHNOTENUMBER and ITEM
eg:
0001, Item1
0001, Item2
0001, Item3
0002, Item1
etc....
I need to summarise this information in another table:
DESPATCHNOTENUMBER and ITEMCOUNT
eg:
0001,3
0002,1
etc...
The summary table already has the rows for DESPATCHNOTES 0001 and 0002, so I need an UPDATE statement to insert the ITEMCOUNT where the ITEMCOUNT is currently NULL.
My own pitiful attempts so far have only resulted in DESPATCHNOTES 0001 & 0002 both having an item count of 4, which is obviously wrong. I've been reading BOL and I admit I'm stuck - I think i've been staring at it too long. Can you guys help?
Many thanks in advance,
Tim
November 17, 2004 at 5:22 am
Something like this might help you:
UPDATE B SET ItemCount = A.RecCnt
FROM tableA A
INNER JOIN (SELECT DespatchNoteNumber, COUNT(*) RecCnt
GROUP BY DespatchNoteNumber) B
ON A.DespatchNoteNumber = B.DespatchNoteNumber
Good Hunting!
AJ Ahrens
webmaster@kritter.net
November 17, 2004 at 5:29 am
...but actually you don't need another table for it
select
DESPATCHNOTENUMBER, count(ITEM) as [ITEM]
from
< your_table >
group by
DESPATCHNOTENUMBER
should also work fine.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
November 17, 2004 at 5:38 am
Thanks guys, I wasn't GROUPING and that was my problem.
What can I say, I'd be scuppered if it wasn't for guys like you out here. Who knows, one day I might be able to return the favour!
Have a great day!
Tim
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply