Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

add select count in query




;with tempPaged AS

(
 select *
      , row_number()
           over(partition by Grouper order by ItemID desc)AS rn2  
,ROW_NUMBER() over (order by itemid) as totrow      
 from DistributionCart_Temp A
where  Dept = Dept  And A.UserId = @UserId
)
, t
as
(
select *
   , CASE rn2
        WHEN 1
           THEN convert(varchar(20), Loyal)
        ELSE '' end as Loyal3
   ,CASE when    Loyalt>'0'
       
from tempPaged
where Dept = 'Grocery' And totrow >= @Lower  AND totrow <= @Upper
)
select *
    , coalesce(nullif(a.Loyal3, '0'), coalesce(b.Loyalt3, '0')) as Loyal3
from t as a
left outer join t as b on b.Grouper = a.Grouper and b.rn2 = a.rn2

order by a.ItemID, a.rn2 asc

#################################################
I would like to add
(SELECT COUNT(*) As to3 FROM Distrib where Dept = Dept  )
to

select *
   , CASE rn2
        WHEN 1
           THEN convert(varchar(20), Loyal)
        ELSE '' end as Loyal3
   ,CASE when    Loyalt>'0'
       
from tempPaged
where Dept = 'Grocery' And totrow >= @Lower  AND totrow <= @Upper

0
Seven price
Asked:
Seven price
  • 5
  • 2
  • 2
6 Solutions
 
ralmadaCommented:
You could try
select *
   , CASE rn2
        WHEN 1
           THEN convert(varchar(20), Loyal)
        ELSE '' end as Loyal3
   ,CASE when    Loyalt>'0'
   ,count(*) over (partition by Dept)
        
from tempPaged
where Dept = 'Grocery' And totrow >= @Lower  AND totrow <= @Upper

Open in new window

0
 
ralmadaCommented:
So basically using count(*) over (partition by Dept)
 
0
 
jogosCommented:
I think it's something like this you want


select *
   , CASE rn2
        WHEN 1
           THEN convert(varchar(20), Loyal)
        ELSE '' end as Loyal3
   ,CASE when    Loyalt>'0'
        (SELECT COUNT(*) FROM Distrib where Distrib.Dept = tempPaged.Dept  )
     end as to3
 from tempPaged
where Dept = 'Grocery' And totrow >= @Lower  AND totrow <= @Upper



0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
ralmadaCommented:
Correction
select *
   , CASE rn2
        WHEN 1
           THEN convert(varchar(20), Loyal)
        ELSE '' end as Loyal3
   ,CASE when    Loyalt>'0' then
        (SELECT COUNT(*) FROM Distrib where Distrib.Dept = tempPaged.Dept) 
     end as to3
 from tempPaged
where Dept = 'Grocery' And totrow >= @Lower  AND totrow <= @Upper

Open in new window

0
 
Seven priceFull StackAuthor Commented:
Coming up with errors for all of them, here is the true query

select *
   , CASE rn2
        WHEN 1
           THEN convert(varchar(20), LoyaltyPrice)
        ELSE '' end as LoyaltyPrice3
   ,CASE when    LoyaltyPrice>'0'
          then  
     end as to3
LTRIM((((caseCost - currentDeal - ShipAmount) + (caseCost * TackOnAmount))  / Pack - RedemptionA))
        Else '0'   end as TotalUnit
 ,count(*) over (partition by DistributionCart_Temp)

from tempPagedProducts

#################

(SELECT COUNT(*) As to3 FROM DistributionCart_Temp where Dept = 'Grocery' )
0
 
ralmadaCommented:
I'm not sure what column DistributionCart_Temp and tempPagedProducts have in common so I've assumed productid. Change it with the correct column name. Maybe it's Dept.

select *
  , CASE rn2
       WHEN 1
          THEN convert(varchar(20), LoyaltyPrice)
       ELSE '' end as LoyaltyPrice3
  ,CASE when    LoyaltyPrice>'0'
         then  
    end as to3
LTRIM((((caseCost - currentDeal - ShipAmount) + (caseCost * TackOnAmount))  / Pack - RedemptionA)) 
        Else '0'   end as TotalUnit
,(SELECT COUNT(*) FROM DistributionCart_Temp where tempPagedProducts.productid = DistributionCart_Temp.productid and Dept = 'Grocery' ) As to3

Open in new window

0
 
ralmadaCommented:
Btw, you have a syntax problem there. check if this is what you're looking for
select * 
  , CASE rn2 
       WHEN 1 
          THEN convert(varchar(20), LoyaltyPrice) 
       ELSE '' end as LoyaltyPrice3 
  ,CASE when    LoyaltyPrice>'0' 
         then   (SELECT COUNT(*) FROM DistributionCart_Temp where tempPagedProducts.productid = DistributionCart_Temp.productid and Dept = 'Grocery' ) 
    end as to3 
,LTRIM((((caseCost - currentDeal - ShipAmount) + (caseCost * TackOnAmount))  / Pack - RedemptionA))  as TotalUnit

Open in new window

0
 
jogosCommented:
Errors on all of them .... could be helpfull to share the error

on a step to step basis .... just start to get your CASE-statement OK just by providing dummy-values

 ,CASE when    LoyaltyPrice>'0'
         then  -1
--- no value was here
else  -9   -- this wasn't there  always a value needed, possible NULL
    end as to3
-- where the  ,    to separate the columns  'end as to3' ends the CASE, but not new CASE starts for the 'end as TotalUnit'-part    
-- LTRIM((((caseCost - currentDeal - ShipAmount) + (caseCost * TackOnAmount))  / Pack - RedemptionA))
        Else '0'   end as TotalUnit
0
 
Seven priceFull StackAuthor Commented:
Thanks all have a merry x-mas
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now