count of orderitems.orderid instead (or in addition)

this query finds a count of
products.productid

want to count orderids
is there a way there can be a count of orderitems.orderid  instead (or in addition)
SELECT
p.productid,p.title, (ISNULL(CAST(P_Cnt.prod_Cnt AS VARCHAR(20)), 'No Orders'))as aquantity,p.internalsku,p.internalsku2,p.internalsku3,p.folderid,p.inwarehouse,p.inwarehouse2,p.inwarehouse3,p.ourinwarehouse,p.ourinwarehouse2,p.ourinwarehouse3,p.sentquantity,p.sentquantity2,p.sentquantity3,p.estimatedcost,p.cost2,p.cost3,date,date2,date3,estimateddate,estimateddate2,estimateddate3,i.orderid
from products p
left join orderitems i
  on i.productid= p.productid
left outer join (
select p2.productid, count(*) prod_Cnt from orderitems i
inner join orders o on o.orderid = i.orderid
right join payments y on o.orderid = y.orderid
inner join products p2 on i.productid = p2.productid
where o.statusid in (9,10,90,180,260)
group by p2.productid)
P_Cnt on p.productid = P_Cnt.productid
where p.folderid = 119 order by p.productid desc

Open in new window

LVL 1
rgb192Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
sure, I presume you want to count the orderids per productid?
SELECT
p.productid,p.title, (ISNULL(CAST(P_Cnt.prod_Cnt AS VARCHAR(20)), 'No Orders'))as aquantity,p.internalsku,p.internalsku2,p.internalsku3,p.folderid,p.inwarehouse,p.inwarehouse2,p.inwarehouse3,p.ourinwarehouse,p.ourinwarehouse2,p.ourinwarehouse3,p.sentquantity,p.sentquantity2,p.sentquantity3,p.estimatedcost,p.cost2,p.cost3,date,date2,date3,estimateddate,estimateddate2,estimateddate3,i.orderid
, (select count(*) from orderitems x where x.productid = p.productid ) order_count
from products p
left join orderitems i
  on i.productid= p.productid
left outer join (
select p2.productid, count(*) prod_Cnt from orderitems i
inner join orders o on o.orderid = i.orderid
right join payments y on o.orderid = y.orderid
inner join products p2 on i.productid = p2.productid
where o.statusid in (9,10,90,180,260)
group by p2.productid)
P_Cnt on p.productid = P_Cnt.productid
where p.folderid = 119 order by p.productid desc

Open in new window

0
 
Shannon_LowderCommented:
It appears you already have this count.  If you add p_cnt.prod_Cnt, it should appear in your final select.  Unless I'm missing something in your code.
0
 
rgb192Author Commented:
>>It appears you already have this count.  If you add p_cnt.prod_Cnt, it should appear in your final select.  Unless I'm missing something in your code.

which line
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
rgb192Author Commented:
I added this to the last line
and o.statusid in (9,10,90,180,260) order by i.orderid desc

and got these errors


Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "o.statusid" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "o.statusid" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "o.statusid" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "o.statusid" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "o.statusid" could not be bound.



because the query provided by angelIII is returning all the ordersitems
but I only want the orderitems that are o.statusid in (9,10,90,180,260)
SELECT
i.orderid,(select count(*) from orderitems x where x.productid = p.productid ) order_count,
p.productid,p.title, (ISNULL(CAST(P_Cnt.prod_Cnt AS VARCHAR(20)), 'No Orders'))as aquantity,p.internalsku,p.internalsku2,p.internalsku3,p.folderid,p.inwarehouse,p.inwarehouse2,p.inwarehouse3,p.ourinwarehouse,p.ourinwarehouse2,p.ourinwarehouse3,p.sentquantity,p.sentquantity2,p.sentquantity3,p.estimatedcost,p.cost2,p.cost3,date,date2,date3,estimateddate,estimateddate2,estimateddate3
from products p
left join orderitems i
  on i.productid= p.productid
left outer join (
select p2.productid, count(*) prod_Cnt from orderitems i
inner join orders o on o.orderid = i.orderid
right join payments y on o.orderid = y.orderid
inner join products p2 on i.productid = p2.productid
where o.statusid in (9,10,90,180,260)
group by p2.productid)
P_Cnt on p.productid = P_Cnt.productid
where p.folderid = 119 and o.statusid in (9,10,90,180,260) order by i.orderid desc

Open in new window

0
 
Shannon_LowderCommented:
Line 8 through 13 appears to be a count by productid.  
0
 
Shannon_LowderCommented:
I don't think you can reference the o (orders) table from the main table, since you define that alias inside the subquery called p_cnt.  is the statid column also defined in products or orderitems?
0
 
rgb192Author Commented:
>>is the statid column also defined in products or orderitems?

there is no column in either orderitems or products that is 'count'
0
 
Shannon_LowderCommented:
But in your subquery

select p2.productid, count(*) prod_Cnt from orderitems i
inner join orders o on o.orderid = i.orderid
right join payments y on o.orderid = y.orderid
inner join products p2 on i.productid = p2.productid
where o.statusid in (9,10,90,180,260)
group by p2.productid

You have defined count(*) as prod_cnt.  Since that subquery is defined as P_Cnt, you can reference P_Cnt.prod_cnt in the main query.  The following code should run, and the last column should now be prod_cnt.

---
Shannon Lowder
Database Engineer
SELECT
     p.productid
   , p.title
   , (ISNULL(CAST(P_Cnt.prod_Cnt AS VARCHAR(20)), 'No Orders')) as aquantity
   , p.internalsku  
   , p.internalsku2
   , p.internalsku3
   , p.folderid
   , p.inwarehouse
   , p.inwarehouse2
   , p.inwarehouse3
   , p.ourinwarehouse
   , p.ourinwarehouse2
   , p.ourinwarehouse3
   , p.sentquantity
   , p.sentquantity2
   , p.sentquantity3
   , p.estimatedcost
   , p.cost2
   , p.cost3
   , date
   , date2
   , date3
   , estimateddate
   , estimateddate2
   , estimateddate3
   , i.orderid
   --new line here
   , P_Cnt.prod_cnt 
from products p
left join orderitems i
  on i.productid= p.productid
left outer join (
   select 
        p2.productid
	  , count(*) prod_Cnt 
   from orderitems i
   inner join orders o 
      on o.orderid = i.orderid
   right join payments y 
      on o.orderid = y.orderid
   inner join products p2 
      on i.productid = p2.productid
   where 
      o.statusid in (9,10,90,180,260)
   group by 
      p2.productid) P_Cnt 
   on p.productid = P_Cnt.productid
where 
   p.folderid = 119 
order by 
   p.productid desc

Open in new window

0
 
rgb192Author Commented:
thanks

that query counts the productids that are
inner join orders o on o.orderid = i.orderid
right join payments y on o.orderid = y.orderid
inner join products p2 on i.productid = p2.productid
where o.statusid in (9,10,90,180,260)


but it shows all the orderids


is there a way to only show the orderids
that
inner join orders o on o.orderid = i.orderid
right join payments y on o.orderid = y.orderid
inner join products p2 on i.productid = p2.productid
where o.statusid in (9,10,90,180,260)
0
 
Shannon_LowderCommented:
Change the JOIN on line 33 from a LEFT OUTER to an INNER.  That should then only return the records that match a record with a count.

---
Shannon Lowder
Database Engineer
0
 
rgb192Author Commented:
getting closer

returned less results of 'old orderids'
SELECT
     p.productid
   , p.title
   , (ISNULL(CAST(P_Cnt.prod_Cnt AS VARCHAR(20)), 'No Orders')) as aquantity
   , p.internalsku  
   , p.internalsku2
   , p.internalsku3
   , p.folderid
   , p.inwarehouse
   , p.inwarehouse2
   , p.inwarehouse3
   , p.ourinwarehouse
   , p.ourinwarehouse2
   , p.ourinwarehouse3
   , p.sentquantity
   , p.sentquantity2
   , p.sentquantity3
   , p.estimatedcost
   , p.cost2
   , p.cost3
   , date
   , date2
   , date3
   , estimateddate
   , estimateddate2
   , estimateddate3
   , i.orderid
   --new line here
   , P_Cnt.prod_cnt 
from products p
left join orderitems i
  on i.productid= p.productid
inner join (
   select 
        p2.productid
          , count(*) prod_Cnt 
   from orderitems i
   inner join orders o 
      on o.orderid = i.orderid
   right join payments y 
      on o.orderid = y.orderid
   inner join products p2 
      on i.productid = p2.productid
   where 
      o.statusid in (9,10,90,180,260)
   group by 
      p2.productid) P_Cnt 
   on p.productid = P_Cnt.productid
where 
   p.folderid = 119 
order by 
   p.productid desc

Open in new window

0
 
Shannon_LowderCommented:
Is the sub query returning data for more rows than it's supposed to?  If your outer query is returning more rows than you want I woud start by double checking that inner query.
---
Shannon Lowder
Database Engineer
0
 
rgb192Author Commented:
inner query is good

but I am trying to change outter query
0
 
Shannon_LowderConnect With a Mentor Commented:
Let's see if duplicating the join to the orders table, and adding the statusID check again to the outer query resolves your issue.  Without taking a closer look at your table data, I would think this has the same results as before.  But let's try it to see if it removes the records you want removed.

---
Shannon Lowder
Database Engineer
SELECT
     p.productid
   , p.title
   , (ISNULL(CAST(P_Cnt.prod_Cnt AS VARCHAR(20)), 'No Orders')) as aquantity
   , p.internalsku  
   , p.internalsku2
   , p.internalsku3
   , p.folderid
   , p.inwarehouse
   , p.inwarehouse2
   , p.inwarehouse3
   , p.ourinwarehouse
   , p.ourinwarehouse2
   , p.ourinwarehouse3
   , p.sentquantity
   , p.sentquantity2
   , p.sentquantity3
   , p.estimatedcost
   , p.cost2
   , p.cost3
   , date
   , date2
   , date3
   , estimateddate
   , estimateddate2
   , estimateddate3
   , i.orderid
   --new line here
   , P_Cnt.prod_cnt 
from products p
left join orderitems i
  on i.productid= p.productid
inner join (
   select 
        p2.productid
          , count(*) prod_Cnt 
   from orderitems i
   inner join orders o 
      on o.orderid = i.orderid
   right join payments y 
      on o.orderid = y.orderid
   inner join products p2 
      on i.productid = p2.productid
   where 
      o.statusid in (9,10,90,180,260)
   group by 
      p2.productid) P_Cnt 
   on p.productid = P_Cnt.productid
inner join orders o 
   on o.orderid = i.orderid
where 
   p.folderid = 119 
   AND o.statusid in (9,10,90,180,260)
order by 
   p.productid desc

Open in new window

0
 
rgb192Author Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.