SQL Query

[platypus]

Just wondering if any experts can help shed light on this, trying to get a multiple query count.

I'm wanting to run x amount of (pretty) complex queries in query analyser (mssql), and to get an overall count of returned rows of all 5 queries, if that makes sense.

Eg declare each query as a variable, then select the count from each variable in columns (named appropriately)?

I'll try and be a bit more clear....

declare @Variable varchar
set @Variable = (multiline join query here)

say 5 times each using a different query naturally, and I want to get a total count for each query, in a sort of table such as:

Query1
100

Query2
500

Query3
100000000

 

[GravyMonster]

Once you've got all your variables set, you can retrieve them all in one query:

SELECT @query1Count [Q1Count], @query2Count [Q2Count]...

Is that what you meant?

 

[platypus]

Er, I think so.... Might help if I show a few more of the queries.

declare @totalgprstraffic varchar

set @totalgprstraffic = (select g.logtime as time,
'sim: '+s.simcardnumber as simnumber , 'tel: '+s.telephonenumber as Tel, s.networkoperator as network,
Right(Replicate('0',6) + convert(varchar(6),g.NGRE ) ,6) + ' ' +
Right(Replicate('0',6) + convert(varchar(6),g.NGRN ) ,6)
as Grid,
'lac\cell: '+CONVERT(varchar(6), g.CellLAC) + '-' + CONVERT(varchar(5), g.CellID) as 'last connected LAC/Cell'
from gpslog g
left join mobiledevice m
on g.vehiclefk=m.vehiclefk
left join simcard s
on m.simcardfk=s.simcardid
where g.logtime > dateadd(hour,-168,getutcdate())
and g.celllac is not null)

declare @gprstrafficloggederrors varchar

set @gprstrafficloggederrors = (select g.logtime as time,
'sim: '+s.simcardnumber as simnumber , 'tel: '+s.telephonenumber as Tel, s.networkoperator as network,
Right(Replicate('0',6) + convert(varchar(6),g.NGRE ) ,6) + ' ' +
Right(Replicate('0',6) + convert(varchar(6),g.NGRN ) ,6)
as Grid,
'lac\cell: '+CONVERT(varchar(6), g.CellLAC) + '-' + CONVERT(varchar(5), g.CellID) as 'last connected LAC/Cell'
from gpslog g
left join mobiledevice m
on g.vehiclefk=m.vehiclefk
left join simcard s
on m.simcardfk=s.simcardid
where (g.connectionflags < 7 or g.connectionflags =35) and g.logtime > dateadd(hour,-168,getutcdate())
and g.celllac is not null
order by time asc)

So these queries return a fair amount of data, all of which is useful but if I want that information I can run the queries separately.

So I've got 6 queries at present running that I just want the count from.

So will:

select @totalgprstraffic [@totalgprstrafficCount], @gprstrafficloggederrors [@gprstrafficloggederrorsCount], etc

work in that respect?

Edit: to which I get "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

Getting there, I shall crack on! Cheers for the reply, and any further replies .

 

[philldutton]

Shouldn't you be just doing a count(*) to set each total?

declare @totalgprstraffic varchar

set @totalgprstraffic = (select count(*)
from gpslog g
left join mobiledevice m
on g.vehiclefk=m.vehiclefk
left join simcard s
on m.simcardfk=s.simcardid
where g.logtime > dateadd(hour,-168,getutcdate())
and g.celllac is not null)

 

[platypus]

Shouldn't you be just doing a count(*) to set each total?

That seems an awful lot simpler;

declare @time integer
declare @totalgprstraffic varchar
set @time = -168


set @totalgprstraffic = (select count(*)
from gpslog g
left join mobiledevice m
on g.vehiclefk=m.vehiclefk
left join simcard s
on m.simcardfk=s.simcardid
where g.logtime > dateadd(hour,@time,getutcdate())
and g.celllac is not null)

select @totalgprstraffic as 'total'

would be fine. Getting a syntax error now .

 

[platypus]

I guess that yea, for the overall totals, the actual query content isn't that important, I'd just kind of like to be able to keep the queries intact so that they can individually be run from within the script. Not the end of the world mind.

 

[platypus]

Getting there, I think...

/* set time period required for query by changing @time */
declare @time integer
declare @totalgprstraffic varchar
set @time = -168

set @totalgprstraffic = (select count(*)
from gpslog g
where g.logtime > dateadd(hour,@time,getutcdate())
and g.celllac is not null)

select @totalgprstraffic as 'totalgprstraffic'

Just returns '*' though.

 

[Haircut]

Why is @totalgprstraffic a varchar?
Shouldn't it be an integer if it's a count?

 

[platypus]

Yes it should. I was thinking that because the query returns a varchar it would need the datatype to be that, but it doesn't as its just the count.

Thanks!