Penny Arcade Forums› Help / Advice Forum
The new forums will be named Coin Return (based on the most recent vote)! You can check on the status and timeline of the transition to the new forums here.
The Guiding Principles and New Rules document is now in effect.

Help me write an SQL query

RichyRichy Registered User regular
edited October 2009 in Help / Advice Forum
I have a table with four columns, TWord, Title, Article and AWord. All are integers. I also have a specific integer value i.

I'm trying to write a query to get the distinct pairs Title and Article where the count of distinct words in the union of Tword and AWord is equal to i.

I got so far 
Insert into #Pairs select Title, Article from #WTAW where 
(select count(distinct(select TWord from #WTAW union select AWord from #WTAW) ) from #WTAW group by Title, Article ) = @i
I get an error: Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Any idea how to get around that? Thanks!

sig.gif
Richy on

Posts

  • DeebaserDeebaser on my way to work in a suit and a tie Ahhhh...come on fucking guyRegistered User regular
    edited October 2009
    Richy wrote: »
    I have a table with four columns, TWord, Title, Article and AWord. All are integers. I also have a specific integer value i.

    I'm trying to write a query to get the distinct pairs Title and Article where the count of distinct words in the union of Tword and AWord is equal to i.

    wut?

    Deebaser on
  • DelzhandDelzhand Registered User, Transition Team regular
    edited October 2009
    What are you writing this in? That error message sounds like it's coming from some sort of parser...

    Delzhand on
  • DiscoZombieDiscoZombie Registered User regular
    edited October 2009
    yeah, sounds like an Access error but that doesn't look like Access syntax. by the sound of the error I'd probably break it into two nested queries.

    edit: on second glance/thought, it's already a nested query and that's the problem I think. I'll leave the solution to someone else -_-

    DiscoZombie on
  • DelzhandDelzhand Registered User, Transition Team regular
    edited October 2009
    Looking at your query, I think your problem is that you're using an insert and select in the same statement... I'll take a look when I get home, I can't really do SQL in my head at work.

    Delzhand on
  • DelzhandDelzhand Registered User, Transition Team regular
    edited October 2009
    I need a better description of your data and what you're looking for. I can't really parse it the way you have it phrased. You say you need to "get" data, but you have an insert statement written.

    Delzhand on
  • LewishamLewisham Registered User regular
    edited October 2009
    He's trying to take data he has and shove it into a new table, which you can do with an insert and then select syntax. Without really knowing his schema, intention, or wanting to fine-tooth comb the query/error, I can't see anything desperately wrong with it, but he may be hamstrung by his database implementation.

    However, taking the data and reinserting it is such a bad move I can't even describe it.

    Richy: You need to look up what a "view" is. I would also recommend getting a database book, you're making a really nasty and basic mistake. IIRC, you've asked some database questions before, and your depth of understanding wasn't great then either.

    Lewisham on
  • RichyRichy Registered User regular
    edited October 2009
    Delzhand wrote: »
    What are you writing this in? That error message sounds like it's coming from some sort of parser...
    Sorry I didn't mention that. I'm working with MS SQL Server 2005.

    Richy on
    sig.gif
  • RichyRichy Registered User regular
    edited October 2009
    Delzhand wrote: »
    I need a better description of your data and what you're looking for. I can't really parse it the way you have it phrased. You say you need to "get" data, but you have an insert statement written.
    When I say I want to get data, I mean I need to retrieve it from one table and put it into another one; which Lewisham says is not the greatest idea, I'll look into views.

    The table WTAW has the ID number of Words, Titles, Articles, and more Words. Each title has multiple articles, each article has multiple title, and each title-article pair is there several times with different words. So what I'm looking for is the ID of distinct title-article pairs, where the number of different words in both Word columns combined is a given total i. Is that clearer?

    Richy on
    sig.gif
  • GanluanGanluan Registered User regular
    edited October 2009
    You're getting that error because you're attempting to use Count (which is an aggregate function) on a subquery. To "get around this", you can use a @ table or something similar to hold the results from your subquery prior to your running the aggregate.

    You would probably find it easier to provide a View that exposes this data though, after which the query becomes very simple.

    I'm having a hard time visualizing what this query would be used for however...

    Ganluan on
    TrueAchievements Profile
  • EvylEvyl Registered User regular
    edited October 2009
    A quick and dirty fix would be to split up the subquery/union into two selects:

    Insert into #Pairs select Title, Article from #WTAW
    WHERE (SELECT COUNT(DISTINCT tword) FROM #WTAW GROUP BY Title, Article) = @i
    AND (SELECT COUNT(DISTINCT aword) FROM #WTAW GROUP BY Title, Article) = @i

    Evyl on
Sign In or Register to comment.
Penny Arcade Forums› Help / Advice Forum