SQL Server - count image of particular id - Asked By goldy gupta on 13-Sep-11 03:15 AM

Helo to all i  am running a query like this 
select * from rate_details where userid in(select id from registration where country='India')
output is coming like

id       frm                    to                   charge    per      doll        prsn         userid
8 02-Sep-2011 23-Sep-2011 € 224 4 € 600 anyone  12
9 08-Sep-2011 26-Sep-2011 € 3000 1200 € 6000 hdf          13

Now i have a table  tablephoto in which  i have adata of bothuserid

id      userid         Image1                  image2       image3
1         12           BlueImage2.gif        null            BlueImage2.gif 
1         13          BlueImage3.gif        null            null

Now i also wants to count image  quantity of particalar userid.. so plz tell how i  edit my query to get output like  this

id       frm                    to                   charge    per      doll        prsn         userid    imagecount
8 02-Sep-2011 23-Sep-2011 € 224 4 € 600 anyone  12             2
9 08-Sep-2011 26-Sep-2011 € 3000 1200 € 6000 hdf          13              1


plz help 
Sreekumar P replied to goldy gupta on 13-Sep-11 03:21 AM
hi,

here u can use this Query

select * ,
(Select count(tp.id) from tablephoto tp where rate_details.userid=tp.userid group by tp.userid) as ImageCount
from rate_details where userid in(select id from registration where country='India')
goldy gupta replied to Sreekumar P on 13-Sep-11 03:27 AM
for both user id i.e      12 and 13  the count is coming  one  as 


user 12  has  two  images   and user  13 has only on image

so it should come like this

userid     Imagecount
12             2
13              1 
aneesa replied to goldy gupta on 13-Sep-11 03:35 AM
select  id  ,  frm        ,   to         ,  charge  ,  per  ,   Doll     ,   prsn  ,   userid, COUNT(ISNULL(IMAGE1,0))+COUNT

(ISNULL(IMAGE2,0))+COUNT (ISNULL(IMAGE6,0))  AS IMAGECOUNT  from rate_details where userid in(select id from registration where country='India')  GROUP BY  id  ,  frm        ,   to         ,  charge  ,  per  ,   Doll     ,   prsn  ,   userid

from rate_details where userid in(select id from registration where country='India')  GROUP BY  id  ,  frm        ,   to         ,  charge  ,  per  ,   Doll     ,   prsn  ,   userid
Sreekumar P replied to goldy gupta on 13-Sep-11 03:37 AM
are u sure that the Photo table contains 2 records  for userid 12...

please check that...


and try this Optimized query too


SELECT RD.* ,tblImageCount.ImageCount
FROM rate_details RD
CROSS APPLY
(
SELECT count(tp.id) as ImageCount FROM tablephoto tp WHERE RD.userid=tp.userid GROUP BY tp.userid
) as tblImageCount
WHERE userid IN(SELECT id FROM registration WHERE country='India')
goldy gupta replied to aneesa on 13-Sep-11 03:41 AM
I don't want to count NULL  as its counting null also
goldy gupta replied to Sreekumar P on 13-Sep-11 03:47 AM
again same result coming means count 1 is coming

suppose i have like this

id     userid            image1        image2        image3

1        12                 yes              null              yes
2        13                 null               yes              null


now userid 12  has uploaded two images  i.e   image1        and image3
now count should come  2  for userid  12

and  count for userid  13  shold be  1 
aneesa replied to goldy gupta on 13-Sep-11 03:55 AM

select rate_details.id , rate_details.frm, rate_details.to , rate_details.charge , rate_details.per , rate_details.Doll , rate_details.prsn , rate_details.userid, COUNT(ISNULL(IMAGE1,0))+COUNT (ISNULL(IMAGE2,0))+COUNT (ISNULL(IMAGE6,0)) AS IMAGECOUNT from rate_details,tablephoto where rate_details.userid in(select id from registration where country='India') AND IMAGE1 IS NOT NULL OR IMAGE2 IS NOT NULL OR IMAGE3 IS NOT NULL GROUP BY rate_details.id , rate_details.frm , rate_details.to , rate_details.charge , rate_details.per , rate_details.Doll , rate_details.prsn , rate_details.userid

goldy gupta replied to aneesa on 13-Sep-11 04:09 AM
Its counting wrong
Sreekumar P replied to goldy gupta on 13-Sep-11 04:42 AM
hi,

den try this one..

SELECT tbl.id
,tbl.frm
,tbl.to
,tbl.charge
,tbl.per
,tbl.doll
,tbl.prsn
,tbl.userid
,Image1Count+Image2Count+Image3Count as ImageCount
(
SELECT RD.*
,CASE WHEN TP.Image1 IS NULL THEN 1 ELSE 0 END as Image1Count
,CASE WHEN TP.Image2 IS NULL THEN 1 ELSE 0 END as Image2Count
,CASE WHEN TP.Image3 IS NULL THEN 1 ELSE 0 END as Image3Count
FROM rate_details RD
INNER JOIN tablephoto TP ON RD.userid=TP.userid
WHERE userid IN(SELECT id FROM registration WHERE country='India')
) as tbl
aneesa replied to goldy gupta on 13-Sep-11 04:48 AM
it's because you have to add join to the query

select rate_details.id , rate_details.frm, rate_details.to , rate_details.charge , rate_details.per , rate_details.Doll , rate_details.prsn , rate_details.userid, COUNT(ISNULL(IMAGE1,0))+COUNT (ISNULL(IMAGE2,0))+COUNT (ISNULL(IMAGE6,0)) AS IMAGECOUNT from rate_details,tablephoto where rate_details.userid in(select id from registration where country='India') and rate_details.id = tablephoto .id  AND IMAGE1 IS NOT NULL OR IMAGE2 IS NOT NULL OR IMAGE3 IS NOT NULL GROUP BY rate_details.id , rate_details.frm , rate_details.to , rate_details.charge , rate_details.per , rate_details.Doll , rate_details.prsn , rate_details.userid