question

Jimko1-7792 avatar image
0 Votes"
Jimko1-7792 asked Jimko1-7792 action

COUNT occurence of X,Y across columns

Hi all,
SQL noob here :) I currently have data presented in such a format where col 1 = number of trips 2 = origin postcode 3 = destination (UK) post code,

1 2 3

1 PR5 0XD AB23 8HG
1 PR5 0XD B24 8TG
1 PR5 0XD B24 8TG
1 PR5 0XD B24 8TG
1 PR5 0XD B24 9QJ
1 PR5 0XD B6 7AX
1 PR5 0XD BA4 4PW
1 PR5 0XD BA4 4PW
1 PR5 0XD BB1 2PT
1 PR5 0XD BB1 2PT
1 PR5 0XD BB1 2PT
1 PR5 0XD BB1 2PT
1 PR5 0XD BB1 2PT
1 PR5 0XD BB1 2PT
1 PR5 0XD BB1 2PT
1 PR5 0XD BB1 2PT
1 PR5 0XD BB1 2PT


I need to write a query so the information is grouping the occurences of the trip as so;

for example below, where 9,4 are no. of occurences in the dataset of the pair.

9 PR5 0XD BB1 2PT
4 PR5 0XD B24 8TG

and so on...


I tried this script but it output the top results set, with no grouping,

 SELECT count (load_id) as number trips, origin_zip,Destination_Zip
 FROM [Rep_UK].[dbo].[Main_table_UK]
     WHERE Origin_Zip = ('pr5 0xd')
          GROUP BY Load_ID,Load_Carrier_ID,origin_zip,Destination_Zip
          HAVING COUNT(*) >= 1;

any help much appreciated!!!
thanks
Jamie


sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

sorry if the table isnt formatting properly should be read as EG;


1 | PR5 0XD | AB23 8HG

0 Votes 0 ·

1 Answer

Viorel-1 avatar image
1 Vote"
Viorel-1 answered Jimko1-7792 commented

Did you also try the next query?

 SELECT count (load_id) as [number trips], origin_zip, Destination_Zip
 FROM [Rep_UK].[dbo].[Main_table_UK]
 WHERE Origin_Zip = 'pr5 0xd'
 GROUP BY origin_zip, Destination_Zip
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

superb ,thats what I need thanks very much

0 Votes 0 ·