Make a Extra COUNT Coulmn on result

Hi Champs!
This is probably simple for you champs, but!
I hava tricky SQL query I cannot get to work.
I have a Query from two tables A and B:
I Make a SELECT
A.time , B.name , B.number , B.ammount , A.date , A.transactionNumber
This “TransactionNumber” is in table A and B
So a readout looks like this:
A.time , B.name , B.number , B.ammount , A.transactionNumber
14:00 -- toy1 -- 23423 -- 1 -- 15889
14:00 -- toy2 -- 23488 -- 2 -- 15889
14:04 -- toy8 -- 11423 -- 5 -- 15890
14:10 -- toy2 -- 23488 -- 10 -- 15891
14:10 -- toy3 -- 23473 -- 1 -- 15891
14:10 -- toy6 -- 11342 -- 17 -- 15891
14:10 -- toy9 -- 23563 -- 2 -- 15891
14:12 -- toy0 -- 23423 -- 1 -- 15892
14:12 -- toy4 -- 23423 -- 3 -- 15892
So I need to make a one New Extra column after A.transactionNumber, that
counts the that similar numbers; like this:
A.time , B.name , B.number , B.ammount , A.transactionNumber , Extra New
column
14:00 -- toy1 -- 23423 -- 1 -- 15889
-- 1 --
14:00 -- toy2 -- 23488 -- 2 -- 15889
-- 2 --
14:04 -- toy8 -- 11423 -- 5 -- 15890
-- 1 --
14:10 -- toy2 -- 23488 -- 10 -- 15891
-- 1 --
14:10 -- toy3 -- 23473 -- 1 -- 15891
-- 2 --14:10 -- toy6 -- 11342 -- 17
-- 15891 -- 3 --
14:10 -- toy9 -- 23563 -- 2 -- 15891
-- 4 --
14:12 -- toy0 -- 23423 -- 1 -- 15892
-- 1 --
14:12 -- toy4 -- 23423 -- 3 -- 15892
-- 2 --
I’ve tried with the a Count (*) , but cannot seem to get it to work.
Any help would be much appreciated.
/Many Thanks
Kurlan"Kutlan" <Kutlan@.discussions.microsoft.com> wrote in message
news:B7791534-DEBC-48CD-AD63-F6F167897B60@.microsoft.com...
> Hi Champs!
> This is probably simple for you champs, but!
> I hava tricky SQL query I cannot get to work.
> I have a Query from two tables A and B:
> I Make a SELECT
> A.time , B.name , B.number , B.ammount , A.date , A.transactionNumber
> This "TransactionNumber" is in table A and B
> So a readout looks like this:
> A.time , B.name , B.number , B.ammount , A.transactionNumber
> 14:00 -- toy1 -- 23423 -- 1 -- 15889
> 14:00 -- toy2 -- 23488 -- 2 -- 15889
> 14:04 -- toy8 -- 11423 -- 5 -- 15890
> 14:10 -- toy2 -- 23488 -- 10 -- 15891
> 14:10 -- toy3 -- 23473 -- 1 -- 15891
> 14:10 -- toy6 -- 11342 -- 17 -- 15891
> 14:10 -- toy9 -- 23563 -- 2 -- 15891
> 14:12 -- toy0 -- 23423 -- 1 -- 15892
> 14:12 -- toy4 -- 23423 -- 3 -- 15892
>
> So I need to make a one New Extra column after A.transactionNumber, that
> counts the that similar numbers; like this:
> A.time , B.name , B.number , B.ammount , A.transactionNumber , Extra New
> column
> 14:00 -- toy1 -- 23423 -- 1 -- 15889
> -- 1 --
> 14:00 -- toy2 -- 23488 -- 2 -- 15889
> -- 2 --
> 14:04 -- toy8 -- 11423 -- 5 -- 15890
> -- 1 --
> 14:10 -- toy2 -- 23488 -- 10 -- 15891
> -- 1 --
> 14:10 -- toy3 -- 23473 -- 1 -- 15891
> -- 2 --14:10 -- toy6 -- 11342 -- 17
> -- 15891 -- 3 --
> 14:10 -- toy9 -- 23563 -- 2 -- 15891
> -- 4 --
> 14:12 -- toy0 -- 23423 -- 1 -- 15892
> -- 1 --
> 14:12 -- toy4 -- 23423 -- 3 -- 15892
> -- 2 --
>
> I've tried with the a Count (*) , but cannot seem to get it to work.
> Any help would be much appreciated.
>
> /Many Thanks
> Kurlan
>
Would a GROUP BY WITH ROLLUP work for you? Check it out in the BOL.
Your query would then look something like: (note untested).
SELECT A.time ,
B.name ,
B.number ,
B.ammount ,
A.date ,
A.transactionNumber
COUNT(A.transactionNumber)
FROM TableA a
JOIN TableB b ON a.TransactionNumber = b.TransactionNumber
GROUP BY a.time, b.name, b.number, b.ammount, a.date, a.transactionNumber
WITH ROLLUP
Rick|||What is the primary key? Please post DDL for the table otherwise any
answers you get will just be guesswork. It's also more helpful to
include sample data as INSERT statements rather than tabular text.
Here's an example from the Pubs database that may give you a clue.
SELECT A1.au_id, A1.au_lname, A1.au_fname, A1.state,
COUNT(*) AS num
FROM Authors AS A1
JOIN Authors AS A2
ON A1.state = A2.state
AND A1.au_id >= A2.au_id
GROUP BY A1.au_id, A1.au_lname, A1.au_fname, A1.state
ORDER BY A1.state, A1.au_id ;
David Portas
SQL Server MVP
--