Sum field language translation craziness

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

Sum field language translation craziness

Author

Topic

seandamnit
Starting Member

1 Post

Posted - 2013-03-13 : 02:32:02

Hey kids,

I'm attempting to pull a data set in to excel that displays current active inventory in our system, by store and SKU. Getting the actual store's name is tricky...the system doesn't give you the store's name directly, instead it gives you a column called StoreNameID in the iQClerk_Stores table, which you need to join with ReferenceID from a table called LanguageTranslations, and use the FieldText column from that table to get the actual store name. Complicated right? It looks something like this:


SELECT LanguageTranslations.FieldText, 
  iQclerk_Stores.StoreID, 
  iQclerk_GlobalProducts.ProductIdentifier, 
  iQclerk_StoresAndNonSellableProducts.Quantity

FROM ArchTelecom.dbo.iQclerk_GlobalProducts iQclerk_GlobalProducts, 
  ArchTelecom.dbo.iQclerk_Stores iQclerk_Stores, 
  ArchTelecom.dbo.iQclerk_StoresAndNonSellableProducts iQclerk_StoresAndNonSellableProducts, 
  ArchTelecom.dbo.LanguageTranslations LanguageTranslations

WHERE iQclerk_StoresAndNonSellableProducts.StoreID = iQclerk_Stores.StoreID AND 
  iQclerk_Stores.StoreNameID = LanguageTranslations.ReferenceID AND 
  iQclerk_GlobalProducts.GlobalProductID = iQclerk_StoresAndNonSellableProducts.GlobalProductID AND 
  ((iQclerk_Stores.Enabled=1))

And the expected result looks like this:


Quantity FieldText StoreID ProductIdentifier
1        GrandAve  37      ACCCCCC1101
1        GrandAve  37      ACCCCCC1101
1        Dover     52      ACCCCCC1105
1        Main      37      ACCCCCC1106
.
.
.

Anyway, I'd like to SUM up the Quantity column, but that weird FieldText field doesn't play nice with GROUP BY it seems. So when I change the code to look like this:


SELECT iQclerk_Stores.StoreID, 
  iQclerk_GlobalProducts.ProductIdentifier,
  LanguageTranslations.FieldText
  Sum(iQclerk_StoresAndNonSellableProducts.Quantity)

FROM ArchTelecom.dbo.iQclerk_GlobalProducts iQclerk_GlobalProducts, 
  ArchTelecom.dbo.iQclerk_Stores iQclerk_Stores, 
  ArchTelecom.dbo.iQclerk_StoresAndNonSellableProducts iQclerk_StoresAndNonSellableProducts, 
  ArchTelecom.dbo.LanguageTranslations LanguageTranslations

WHERE iQclerk_StoresAndNonSellableProducts.StoreID = iQclerk_Stores.StoreID AND 
  iQclerk_Stores.StoreNameID = LanguageTranslations.ReferenceID AND 
  iQclerk_GlobalProducts.GlobalProductID = iQclerk_StoresAndNonSellableProducts.GlobalProductID AND 
  ((iQclerk_Stores.Enabled=1))

GROUP BY iQclerk_Stores.StoreID, 
  iQclerk_GlobalProducts.ProductIdentifier

I get this error:

https://www.dropbox.com/s/ripkhnlh1yuz9sc/field%20text.png

Hope this is enough info. Any help is much appreciated!

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-13 : 02:52:42

GROUP BY iQclerk_Stores.StoreID,
iQclerk_GlobalProducts.ProductIdentifier,
LanguageTranslations.FieldText

If you want sum of quantity based on StoreId and ProductIdentifier only.... then use Over() clause with SUM aggregate

Better to post error message( by copy and paste).. some companies might not allow external links.....

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources