Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 [ Solved ]Average Function!

Author  Topic 

ShayaN_
Starting Member

22 Posts
Posted - 2011-10-05 : 03:26:36
Query
quote:
SELECT A.CORPORATE_CODE, A.DISTRIBUTOR_NAME, A.BRAND, A.SKU, A.SKU_DESC, A.YEAR, A.JCNO,
MAX(B.GRAND_TOTAL) TOTAL_POPS,
ISNULL(MAX(SS.SCH),0) SCHEDULE_POPS,
ISNULL(COUNT(DISTINCT(CASE WHEN A.CM_COUNT = 1 THEN (A.POP_CODE) END)),0) BUY_ONCE,
ISNULL(COUNT(DISTINCT(CASE WHEN A.CM_COUNT = 2 THEN (A.POP_CODE) END)),0) BUY_TWICE,
ISNULL(COUNT(DISTINCT(CASE WHEN A.CM_COUNT = 3 THEN (A.POP_CODE) END)),0) BUY_THRICE,
ISNULL(COUNT(DISTINCT(CASE WHEN A.CM_COUNT >= 4 THEN (A.POP_CODE) END)),0) BUY_FOURTH_AND_MORE
FROM
(SELECT DISTINCT P.TOWN+'-'+P.DISTRIBUTOR+'-'+P.LOCALITY+'-'+P.SLOCALITY+'-'+P.POP POP_CODE, D.REGION REGION, T.SDESC TOWN,
D.CORPORATE_CODE CORPORATE_CODE, D.DISTRIBUTOR DISTRIBUTOR, D.NAME DISTRIBUTOR_NAME, P.NAME POP_NAME, P5.LDESC BRAND,
S.SKU SKU, S.LDESC SKU_DESC, JC.YEAR, JC.JCNO, COUNT(DISTINCT P.TOWN+'-'+P.DISTRIBUTOR+'-'+P.LOCALITY+'-'+P.SLOCALITY+'-'+P.POP+'-'+CM.DOC_NO) CM_COUNT
FROM Sidat_Host1.DBO.CASHMEMO CM, Sidat_Host1.DBO.CASHMEMO_DETAIL CD, Sidat_Host1.DBO.SKU S, Sidat_Host1.DBO.JC_WEEK JC,
Sidat_Host1.DBO.POP P, Sidat_Host1.DBO.DISTRIBUTOR D, Sidat_Host1.DBO.TOWN T, Sidat_Host1.DBO.PROD_LEVEL5 P5
WHERE D.DISTRIBUTOR = P.DISTRIBUTOR
AND D.TOWN = T.TOWN
AND CM.DISTRIBUTOR+CM.DOC_NO = CD.DISTRIBUTOR+CD.DOC_NO
AND CM.DOC_DATE = CD.DOC_DATE
AND CD.SKU = S.SKU
AND S.PROD1+S.PROD2+S.PROD3+S.PROD4+S.PROD5 = P5.PROD1+P5.PROD2+P5.PROD3+P5.PROD4+P5.PROD5
AND CM.TOWN = P.TOWN
AND CM.DISTRIBUTOR = P.DISTRIBUTOR
AND CM.LOCALITY = P.LOCALITY
AND CM.SLOCALITY = P.SLOCALITY
AND CM.POP=P.POP
AND CM.VISIT_TYPE='02'
AND D.DISTRIBUTOR = '30634A'
AND CM.DELV_DATE
BETWEEN JC.START_DATE AND JC.END_DATE
AND CM.DELV_DATE BETWEEN CONVERT(DATETIME,'03/01/2011',103)
AND CONVERT(DATETIME,'02/10/2011',103)
GROUP BY P.TOWN+'-'+P.DISTRIBUTOR+'-'+P.LOCALITY+'-'+P.SLOCALITY+'-'+P.POP, D.REGION, T.SDESC, D.CORPORATE_CODE,
D.DISTRIBUTOR, D.NAME, P.NAME, P5.LDESC, S.SKU, S.LDESC , JC.YEAR, JC.JCNO HAVING SUM(CD.AMOUNT) > '0') A,
(SELECT COUNT(DISTINCT(TOWN+'-'+DISTRIBUTOR+'-'+LOCALITY+'-'+SLOCALITY+'-'+POP)) GRAND_TOTAL
FROM POP
WHERE DISTRIBUTOR = '30634A'
AND ACTIVE = '1') B,
(SELECT DISTINCT D.DISTRIBUTOR, C.SKU, S.LDESC SKU_DESC, C.YEAR, C.JCNO, MAX(C.SCH) SCH
FROM Sidat_Host1.DBO.SKU S, Sidat_Host1.DBO.DISTRIBUTOR D,
(SELECT DISTINCT SPP.DISTRIBUTOR DISTRIBUTOR, E.SKU SKU, JC.YEAR, JC.JCNO,
COUNT(DISTINCT SPP.TOWN+'-'+SPP.DISTRIBUTOR+'-'+SPP.LOCALITY+'-'+SPP.SLOCALITY+'-'+SPP.POP) SCH
FROM Sidat_Host1.DBO.POP_STATUS SPP, Sidat_Host1.DBO.POP P, Sidat_Host1.DBO.JC_WEEK JC,
(SELECT D.DISTRIBUTOR, D.SELL_CATEGORY, D.SKU
FROM Sidat_Host1.DBO.SKU_CATEGORY D
WHERE D.SKU_INDEX <> '0'
AND D.DISTRIBUTOR = '30634A'
) E
WHERE SPP.DISTRIBUTOR = '30634A'
AND SPP.TOWN = P.TOWN
AND SPP.DISTRIBUTOR = P.DISTRIBUTOR
AND SPP.LOCALITY = P.LOCALITY
AND SPP.SLOCALITY = P.SLOCALITY
AND SPP.POP= P.POP
AND SPP.STATUS_DATE
BETWEEN CONVERT(DATETIME, '03/01/2011',103) AND CONVERT(DATETIME, '02/10/2011',103)
AND P.ACTIVE = '1'
AND SPP.DISTRIBUTOR+'-'+SPP.SELL_CATEGORY = E.DISTRIBUTOR+'-'+E.SELL_CATEGORY
AND SPP.STATUS_DATE BETWEEN JC.START_DATE AND JC.END_DATE GROUP BY SPP.DISTRIBUTOR, E.SKU , JC.YEAR, JC.JCNO ) C
WHERE C.SKU = S.SKU
AND D.DISTRIBUTOR = '30634A'
AND C.DISTRIBUTOR = D.DISTRIBUTOR
GROUP BY D.DISTRIBUTOR, C.SKU, S.LDESC , C.YEAR, C.JCNO ) SS
WHERE A.DISTRIBUTOR+'-'+A.SKU *= SS.DISTRIBUTOR+'-'+SS.SKU
AND A.YEAR *= SS.YEAR AND A.JCNO *= SS.JCNO
GROUP BY A.CORPORATE_CODE, A.DISTRIBUTOR_NAME, A.BRAND, A.SKU, A.SKU_DESC , A.YEAR, A.JCNO
ORDER BY A.JCNO

Result:
CORPORATE_CODE DISTRIBUTOR_NAME BRAND SKU SKU_DESC YEAR JCNO TOTAL_POPS SCHEDULE_POPS BUY_ONCE BUY_TWICE BUY_THRICE BUY_FOURTH_AND_MORE
30634A ( ORANGI ) BB A1 65108589 BB A1 BLCK TEA PKT 320X18G 2011 06 1652 624 134 18 5 0
30634A (ORANGI ) BB A1 65108590 BB A1 BLCK TEA PKT 120X95G 2011 06 1652 624 74 17 2 0
30634A (ORANGI ) BB A1 65108591 BB A1 BLCK TEA PKT 24X375G 2011 06 1652 624 5 0 0 0

What i need is Average of these 3 last column, as i know that Avg function is work only on 1 column, can any one help me regarding this ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-10-05 : 04:14:07
instead of using AG function you can do

(BUY_ONCE + BUY_TWICE + BUY_THRICE + BUY_FOURTH_AND_MORE)/4.0


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

ShayaN_
Starting Member

22 Posts
Posted - 2011-10-05 : 05:20:30
quote:
Originally posted by visakh16

instead of using AG function you can do

(BUY_ONCE + BUY_TWICE + BUY_THRICE + BUY_FOURTH_AND_MORE)/4.0


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




this is wot i inserted
MAX(BUY_ONCE+BUY_TWICE+BUY_THRICE+BUY_FOURTH_AND_MORE)/4.0 TOTAL_AVG
but still error
Msg 207, Level 16, State 3, Line 1
Invalid column name 'BUY_ONCE'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'BUY_TWICE'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'BUY_THRICE'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'BUY_FOURTH_AND_MORE'.
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2011-10-05 : 05:22:42
quote:
Originally posted by ShayaN_

quote:
Originally posted by visakh16

instead of using AG function you can do

(BUY_ONCE + BUY_TWICE + BUY_THRICE + BUY_FOURTH_AND_MORE)/4.0


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




this is wot i inserted
MAX(BUY_ONCE+BUY_TWICE+BUY_THRICE+BUY_FOURTH_AND_MORE)/4.0 TOTAL_AVG
but still error
Msg 207, Level 16, State 3, Line 1
Invalid column name 'BUY_ONCE'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'BUY_TWICE'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'BUY_THRICE'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'BUY_FOURTH_AND_MORE'.


you have to replace those with your actual column name. What Visakh has shown you is just an example of the syntax

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-10-05 : 05:43:32
quote:
Originally posted by ShayaN_

quote:
Originally posted by visakh16

instead of using AG function you can do

(BUY_ONCE + BUY_TWICE + BUY_THRICE + BUY_FOURTH_AND_MORE)/4.0


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




this is wot i inserted
MAX(BUY_ONCE+BUY_TWICE+BUY_THRICE+BUY_FOURTH_AND_MORE)/4.0 TOTAL_AVG
but still error
Msg 207, Level 16, State 3, Line 1
Invalid column name 'BUY_ONCE'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'BUY_TWICE'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'BUY_THRICE'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'BUY_FOURTH_AND_MORE'.

why you're using MAX?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

ShayaN_
Starting Member

22 Posts
Posted - 2011-10-05 : 06:04:44
quote:
Originally posted by visakh16

quote:
Originally posted by ShayaN_

quote:
Originally posted by visakh16

instead of using AG function you can do

(BUY_ONCE + BUY_TWICE + BUY_THRICE + BUY_FOURTH_AND_MORE)/4.0


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




this is wot i inserted
MAX(BUY_ONCE+BUY_TWICE+BUY_THRICE+BUY_FOURTH_AND_MORE)/4.0 TOTAL_AVG
but still error
Msg 207, Level 16, State 3, Line 1
Invalid column name 'BUY_ONCE'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'BUY_TWICE'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'BUY_THRICE'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'BUY_FOURTH_AND_MORE'.

why you're using MAX?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




Basically i want to get top 30 or 20 SKU penetration that's why I'm using MAX.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-10-05 : 06:06:45
sorry why should you use max for getting top x?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

ShayaN_
Starting Member

22 Posts
Posted - 2011-10-05 : 06:36:04
quote:
Originally posted by visakh16

sorry why should you use max for getting top x?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




I should use TOP 20 instead of MAX right ?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-10-05 : 06:38:09
yep... as per your posted reqmnt thats what i guess you need to be using

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

ShayaN_
Starting Member

22 Posts
Posted - 2011-10-05 : 06:41:15
quote:
Originally posted by visakh16

yep... as per your posted reqmnt thats what i guess you need to be using

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




Can you please give me complete query how can i use these columns as average and top 20 too.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-10-05 : 07:20:50
[code]SELECT TOP 20 ...,(BUY_ONCE + BUY_TWICE + BUY_THRICE + BUY_FOURTH_AND_MORE)/4.0 AS Avg1
FROM....
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2011-10-05 : 08:58:36
You can't use aliases for calculations in other columns.


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

ShayaN_
Starting Member

22 Posts
Posted - 2011-10-05 : 09:21:19
Thanks visakh16 almost data i got but still the top 20 column is in first can i use top 20 in last if yes then how ?

here is the result of data
quote:

TOTAL_AVERAGE SKU SKU_DESC YEAR JCNO TOTAL_POPS SCHEDULE_POPS
1 65108591 BB A1 BLCK TEA PKT 24X375G 2011 06 1652 624
23 65108590 BB A1 BLCK TEA PKT 120X95G 2011 06 1652 624
39 65108589 BB A1 BLCK TEA PKT 320X18G 2011 06 1652 624
2 65108522 BB SUPREME BLCK TEA PKT 200X40G 2011 06 1652 624
17 65108547 BB SUPREME BLCK TEA PKT B01 120X95G 2011 06 1652 624
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-10-05 : 10:48:40
use ORDER BY your columns DESC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

ShayaN_
Starting Member

22 Posts
Posted - 2011-10-06 : 01:03:38
Thanks Visakh, Thanks A Lot.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-10-06 : 01:12:14
wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site