PROBLEM: I am trying to have the the total products inputted to be added up over a monthly interval. Instead it's putting the total products over 6 months into every row. Here's what I'm getting:[distinct users year] [month] [# of sales channels] [% sales channels] [%UsersLoggedInOnce] [total users added products] [total users added sales orders]7 2014 January 16 100 0 44 217 2014 February 24 100 28.5714285714286 44 215 2014 March 14 100 0 44 214 2014 April 10 100 0 44 2117 2014 May 55 100 23.5294117647059 44 219 2014 June 16 100 55.5555555555556 44 21Look at the row with 44 in it. Those should be divided between each month. This is what it should look like:[distinct users] [year] [month] [# of sales channels] [% sales channels] [%UsersLoggedInOnce] t[otal users added products] [total users added sales orders]7 2014 January 16 100 0 7 217 2014 February 24 100 28.5714285714286 44 215 2014 March 14 100 0 9 214 2014 April 10 100 0 12 2117 2014 May 55 100 23.5294117647059 10 219 2014 June 16 100 55.5555555555556 6 21 SELECT count (distinct(userdata.userid)) as [distinct users] ,datename(year,saleschannel.createddate) as [year] ,datename(month,saleschannel.createddate) as [month] ,count(*) as [# of sales channels] ,(((count (distinct(saleschannel.companyid))) /cast((count (distinct(userdata.userid)))as float)) * 100) as [% sales channels] ,((count(g.companyid)/cast((count (distinct(userdata.userid)))as float)) * 100) as [%UsersLoggedInOnce] ,(SELECT(count(DISTINCT(product.companyid)) ) FROM PRODUCT INNER JOIN saleschannel on product.companyid = saleschannel.companyid ) as [total users added products] ,(SELECT (count(distinct(salesorder.companyid))) from salesorder ) as [total users added sales orders] FROM saleschannel INNER JOIN company on company.companyid = saleschannel.companyid INNER JOIN userdata on userdata.companyid = company.companyid and roleid = 1 AND company.createduserid = userdata.userid LEFT OUTER JOIN userdata g on g.companyid = company.companyid and company.registrationdate = userdata.lastlogindate WHERE saleschannel.createddate > '2013-12-31' GROUP BY month(saleschannel.createddate) , year(saleschannel.createddate) , datename(year,saleschannel.createddate) ,datename(month,saleschannel.createddate) order by year(saleschannel.createddate),month(saleschannel.createddate)
Ive already tried doing a join but whenever I join the products table it either duplicates rows or it multiplies the columns by huge numbers. Been stuck on this for hours and dont know where to go from here.
How do you organize a subquery like the main query
Posted - 2014-06-18 : 13:12:01
