multiple counts and joint tables

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

General SQL Server Forums

New to SQL Server Programming

multiple counts and joint tables

Author

Topic

pavlos
Yak Posting Veteran

59 Posts

Posted - 2011-12-15 : 22:13:25

SELECT
S.STORECODE,
COUNT_1 = (SELECT COUNT(S.ID) FROM TXNDETAIL AS S WHERE H.TRADINGDATE BETWEEN '2011-10-01' AND '2011-10-31'),
COUNT_2 = (SELECT COUNT(H.TRANSACTIONNUMBER) FROM TXNHEADER AS S WHERE H.TRADINGDATE BETWEEN '2011-10-01' AND '2011-10-31')

FROM POS2SAP_Staging.[dbo].TXNDETAIL AS S

JOIN POS2SAP_Staging.[dbo].TXNHEADER AS H
ON H.id = S.id

GROUP BY S.STORECODE

I get this error:
Msg 8120, Level 16, State 1, Line 3
Column 'POS2SAP_Staging.dbo.TXNHEADER.TRADINGDATE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 3
Column 'POS2SAP_Staging.dbo.TXNHEADER.TRADINGDATE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

pavlos
Yak Posting Veteran

59 Posts

Posted - 2011-12-15 : 22:17:51

so ppl :D,
what i want to do is.. count the total amount of customers (which comes from counting the id's in the txndetail table)
and then count the number of items sold (which comes from counting the total transactionnumbers.)

when i tried running the following query.. both of the counts will return the same figure

SELECT
S.STORECODE,
COUNT(S.ID) AS '# Customers',
COUNT(H.TRANSACTIONNUMBER) AS '# Customers'

FROM POS2SAP_Staging.[dbo].TXNDETAIL AS S

JOIN POS2SAP_Staging.[dbo].TXNHEADER AS H
ON H.id = S.id

GROUP BY S.STORECODE

but when i comment a count out and only run 1 count at a time it will work correctly

pavlos
Yak Posting Veteran

59 Posts

Posted - 2011-12-15 : 22:32:35

hey again guys,
relized my original post made no sense.. and worked it out..
please delete

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources