www.webdeveloper.com
Results 1 to 4 of 4

Thread: Group by - what am I forgetting?

  1. #1
    Join Date
    Sep 2003
    Posts
    94

    Group by - what am I forgetting?

    It has been a long time since I've done SQL, but I know there is a way to aggregate items.

    The Query is this:

    Code:
           SELECT 
    		BD.id, 
    		BD.a_id, 
    		(CONVERT(varchar,A.agent_id)+' '+A.fname+' '+A.lname) as name,
    		BD.b_id, 
    		BD.agent_id,
    		(CONVERT(varchar,BDD.billing)+' '+ BDD.description) as billing, 
    		BD.inactive 
    	FROM Univlicval_billing_digits as BD
    	INNER JOIN Univlicval_agents as A
    		ON BD.a_id = A.id 
    	INNER JOIN Univlicval_bd_desc BDD
    		ON BD.b_id = BDD.id 
    	WHERE 
    		(BD.b_id = @b_id)
    It gives me a huge list because there are many places each a_id can be listed. I don't care about the places. I just want to know the a_id's that are associated with the b_id's

    I tried a GROUP BY BD.a_id and I got an error saying that BD.id wasn't in an aggregate function or in the group by.

    Well I ended up having to put EVERYTHING in the GROUP BY to stop getting an error. And of course, it didn't limit the resultset.

    I tried using DISTINCT, but it would seem you can't ask for a distinct column, it is simply at the beginning of the select.... and useless.

    What am I doing wrong? Thanks in advance!

  2. #2
    Join Date
    Jul 2010
    Location
    /ramdisk/
    Posts
    865
    Fist off I do not know what SQL you are using, but you left me a hint.

    I tried a GROUP BY BD.a_id and I got an error saying that BD.id wasn't in an aggregate function or in the group by.
    I think you're using MSSQL or TSQL. Then again- I don't see any schema names. Either way I think I see the issue.

    You have the correct idea, you are just missing something technical.

    You need to put EVERYTHING you select inside of an aggregate function that you have not put inside of the group by clause. I typically use max(); should I actually intend to use max(), I will type MAX() in all uppercase-- as a note to myself that it is important. I do that to distinguish syntax from semantics.

    Be mindful that it WILL select max(); using min() or avg() [etc] may produce a different result; it may also make no difference at all making the whole ordeal of using aggregate functions a syntax pita.

    Try this out:
    Code:
           SELECT 
    		max(BD.id) as id, 
    		BD.a_id, 
    		max((CONVERT(varchar,A.agent_id)+' '+A.fname+' '+A.lname)) as name,
    		max(BD.b_id) as b_id, 
    		max(BD.agent_id) as agent_id,
    		max((CONVERT(varchar,BDD.billing)+' '+ BDD.description)) as billing, 
    		max(BD.inactive) as inactive
    	FROM Univlicval_billing_digits as BD
    	INNER JOIN Univlicval_agents as A
    		ON BD.a_id = A.id 
    	INNER JOIN Univlicval_bd_desc BDD
    		ON BD.b_id = BDD.id 
    	WHERE 
    		(BD.b_id = @b_id)
            GROUP BY BD.a_id
    I share your disappointment with TSQL's clunky aggregate function syntax. I believe it is in part due to TSQL being statically typed.
    I use (, ; : -) as I please- instead of learning the English language specification: I decided to learn Scheme and Java;

  3. #3
    Join Date
    Sep 2003
    Posts
    94
    Thank you!

    I will add that I have a bit column. And found this solution (of which I used the first one and it seems to work):

    Code:
    SELECT
            SUM(CAST(MyBitField AS INT)) AS '1st Technique',
            SUM(CASE(MyBitField) WHEN 1 THEN 1 ELSE 0 END) AS '2nd Technique',
            COUNT(NULLIF(MyBitField,0)) AS '3rd Technique',
    FROM 
            MyTableWhatHasABitColumn
    from secretGeek

  4. #4
    Join Date
    Oct 2009
    Posts
    658
    When you say that it's not limiting the resultset, You mean that you only want records where (BD.b_id = @b_id) ? If so try this

    Code:
     SELECT 
    		BD.*
    	FROM Univlicval_billing_digits as BD
    	WHERE 
    		(BD.b_id = @b_id)
    If that doesn't give you the results you want verify the value of b_id

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles