• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 334
  • Last Modified:

I want to know how many rows the result is

select i.orderid,i.used,o.availability
from orderitems i
inner join orders o on o.orderid = i.orderid
right join payments y on o.orderid = y.orderid
inner join products p on i.productid = p.productid
where o.statusid in (9,10,90,180,260) and y.type is not null
and i.sku=4
and i.productid=64
order by i.orderid desc


I want to know how many rows the result is
0
rgb192
Asked:
rgb192
  • 6
  • 5
  • 3
  • +1
1 Solution
 
Ephraim WangoyaCommented:

use select @@ROWCOUNT after you run the query
select i.orderid,i.used,o.availability
from orderitems i
inner join orders o on o.orderid = i.orderid
right join payments y on o.orderid = y.orderid
inner join products p on i.productid = p.productid
where o.statusid in (9,10,90,180,260) and y.type is not null
and i.sku=4
and i.productid=64
order by i.orderid desc

select @@ROWCOUNT

Open in new window

0
 
Éric MoreauSenior .Net ConsultantCommented:
select @@ROWCOUNT
0
 
Ephraim WangoyaCommented:

or you just want the count only

select count(1) as 'Total Count'
from orderitems i
inner join orders o on o.orderid = i.orderid
right join payments y on o.orderid = y.orderid
inner join products p on i.productid = p.productid
where o.statusid in (9,10,90,180,260) and y.type is not null
and i.sku=4
and i.productid=64
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
rgb192Author Commented:

select i.orderid,i.used,o.availability,count(1) as 'Total Count'
from orderitems i
inner join orders o on o.orderid = i.orderid
right join payments y on o.orderid = y.orderid
inner join products p on i.productid = p.productid
where o.statusid in (9,10,90,180,260) and y.type is not null
and i.sku=4
and i.productid=64


Msg 8120, Level 16, State 1, Line 1
Column 'orderitems.orderid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


I need the query and the rowcount in one query
0
 
Éric MoreauSenior .Net ConsultantCommented:
you have an aggregate (count) without a group by.

can you tell us exactly what you are trying to acheive?
0
 
rgb192Author Commented:
i have a query of which i return results

i want to know how many rows the results are

the number of rows = the number of i.orderid
0
 
Éric MoreauSenior .Net ConsultantCommented:
select i.orderid,i.used,o.availability,(select count(*) from orderitems where orderid = i.orderid) as 'Total Count'
from orderitems i
inner join orders o on o.orderid = i.orderid
right join payments y on o.orderid = y.orderid
inner join products p on i.productid = p.productid
where o.statusid in (9,10,90,180,260) and y.type is not null
and i.sku=4
and i.productid=64

0
 
rgb192Author Commented:
select i.orderid,i.used,o.availability,(select count(*) from orderitems where orderid = i.orderid) as 'Total Count'
from orderitems i
inner join orders o on o.orderid = i.orderid
right join payments y on o.orderid = y.orderid
inner join products p on i.productid = p.productid
where o.statusid in (9,10,90,180,260) and y.type is not null
and i.sku=4
and i.productid=64



there are 6 rows = 6 results  but 'total count' is 1


I need this in one query because I am using this query in php not sql server
0
 
Éric MoreauSenior .Net ConsultantCommented:
can you show a sample of your data and the expected result?
0
 
bitrefCommented:
select i.orderid,i.used,o.availability,(select count(*) from orderitems) as 'Total Count'
from orderitems i
inner join orders o on o.orderid = i.orderid
right join payments y on o.orderid = y.orderid
inner join products p on i.productid = p.productid
where o.statusid in (9,10,90,180,260) and y.type is not null
and i.sku=4
and i.productid=64 

Open in new window

0
 
rgb192Author Commented:
select i.orderid,i.used,o.availability,(select count(*) from orderitems) as 'Total Count'
from orderitems i
inner join orders o on o.orderid = i.orderid
right join payments y on o.orderid = y.orderid
inner join products p on i.productid = p.productid
where o.statusid in (9,10,90,180,260) and y.type is not null
and i.sku=4
and i.productid=64


now 'total count' is the number of rows in all of table orderitems

i want 'total count' to be the number of rows returned
0
 
bitrefCommented:
Declare @Count int;

select @Count = Count(*)
from orderitems i
inner join orders o on o.orderid = i.orderid
right join payments y on o.orderid = y.orderid
inner join products p on i.productid = p.productid
where o.statusid in (9,10,90,180,260) and y.type is not null
and i.sku=4
and i.productid=64 ;

select i.orderid,i.used,o.availability,@Count as 'Total Count'
from orderitems i
inner join orders o on o.orderid = i.orderid
right join payments y on o.orderid = y.orderid
inner join products p on i.productid = p.productid
where o.statusid in (9,10,90,180,260) and y.type is not null
and i.sku=4
and i.productid=64 

Open in new window

0
 
rgb192Author Commented:
Declare @Count int;

select @Count = Count(*)
from orderitems i
inner join orders o on o.orderid = i.orderid
right join payments y on o.orderid = y.orderid
inner join products p on i.productid = p.productid
where o.statusid in (9,10,90,180,260) and y.type is not null
and i.sku=4
and i.productid=64 ;

select i.orderid,i.used,o.availability,@Count as 'Total Count'
from orderitems i
inner join orders o on o.orderid = i.orderid
right join payments y on o.orderid = y.orderid
inner join products p on i.productid = p.productid
where o.statusid in (9,10,90,180,260) and y.type is not null
and i.sku=4
and i.productid=64





works... is there anyway to do it in one query
0
 
bitrefCommented:
With(
select Count(*) as c
from orderitems i
inner join orders o on o.orderid = i.orderid
right join payments y on o.orderid = y.orderid
inner join products p on i.productid = p.productid
where o.statusid in (9,10,90,180,260) and y.type is not null
and i.sku=4
and i.productid=64) as cc
select i.orderid,i.used,o.availability,cc.c as 'Total Count'
from orderitems i
inner join orders o on o.orderid = i.orderid
right join payments y on o.orderid = y.orderid
inner join products p on i.productid = p.productid
where o.statusid in (9,10,90,180,260) and y.type is not null
and i.sku=4
and i.productid=64 

Open in new window

0
 
Éric MoreauSenior .Net ConsultantCommented:
or this:

select i.orderid,i.used,o.availability
,(select Count(*)
from orderitems i2
inner join orders o2 on o2.orderid = i2.orderid
right join payments y2 on o2.orderid = y2.orderid
inner join products p2 on i2.productid = p2.productid
where o2.statusid in (9,10,90,180,260) and y2.type is not null
and i2.sku=4
and i2.productid=64) as 'Total Count'
from orderitems i
inner join orders o on o.orderid = i.orderid
right join payments y on o.orderid = y.orderid
inner join products p on i.productid = p.productid
where o.statusid in (9,10,90,180,260) and y.type is not null
and i.sku=4
and i.productid=64



0
 
rgb192Author Commented:
bitref:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '('.
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'as'.

emoreau:
works and displays the rowcount.  thank you
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now