Group by with multiple table - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Group by with multiple table

Author

Topic

nitinnjp
Starting Member

6 Posts

Posted - 2009-09-22 : 03:01:36

I have 3 tables which has following structures.
1.Tracking_productdetail
proudctId
userid
datetime
type
2.pc_products
productId(pk)
companyId
3.compant_master
companyId(pk)
companyName
Expected output:
productId,userId,datetime(input date Jan 2009),type,companyName

i tried this

SELECT min(cm.companyName) CompanyName,min(CONVERT(CHAR(4), datetime, 100) +
CONVERT(CHAR(4), datetime, 120))
MonthYear,min(Type) Type, count(*) AS FileCount FROM
Tracking_productdetail tp,company_master cm,pc_products pc
WHERE datetime between '8/1/2009' AND '8/31/2009'
and pc.productId=tp.productId and pc.companyId=cm.companyId GROUP BY Type

this gives proper output but i am not able to take a companyId which is of integer datatype in above case companyname and datetime does not affect by min() function. if i use companyId it gives an error

"select list because it is not contained in either an aggregate function or the GROUP BY clause."

plz give me solution as early as possible thanks in advance

nitin patil

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-24 : 02:25:08

SELECT companyid,min(cm.companyName) CompanyName,min(CONVERT(CHAR(4), datetime, 100) +
CONVERT(CHAR(4), datetime, 120))
MonthYear,min(Type) Type, count(*) AS FileCount FROM
Tracking_productdetail tp,company_master cm,pc_products pc
WHERE datetime between '8/1/2009' AND '8/31/2009'
and pc.productId=tp.productId and pc.companyId=cm.companyId GROUP BY Type,companyid

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources