x

SQLserver 2005 join with multiple tables and group by

Hi ,

I have to

SELECT  a.Name,
        a.id,
        a.Type,
        a.date,
        COUNT(b.pac),
        a.ConName
FROM    TableA a
JOIN    TableB b ON a.Taskgenid = b.Taskgenid
                    AND a.formgenid = b.formgenid
JOIN    TableC c ON A.id = c.ID
JOIN    TableD d ON D.Typeid = c.Typeid
WHERE   a.date BETWEEN @Begindate AND @Enddate
        AND a.type IN (1, 2, 3)
GROUP BY a.Name,
        a.id,
        a.Type,
        a.date,
        a.ConName

Is there a simple way instead of selecting all the fields from the select which are not in the aggregate? Urgent!! any help appreciated

more ▼

asked Jan 10, 2011 at 01:32 PM in Default

hanoi gravatar image

hanoi
41 4 4 5

I would suggest that you use 'Please' in your questions, especially when you have already used 'Urgent'. No one is paid to offer advice here and a little politeness costs you nothing when you would like some assistance.
Jan 10, 2011 at 01:59 PM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

There are basically 2 ways to do it. I would like to point out though that from the query in question, there is no way to figure out the relevance of TableC and TableD, because none of the columns from any of these tables are present in the result set. In any case,

Using cross apply:

select
    a.Name, a.id, a.Type, a.date,
    b.pac, a.ConName 
    from TableA a 
    cross apply
    (
        select count(pac) pac from TableB
            where 
                Taskgenid = a.Taskgenid 
                and formgenid = a.formgenid
    ) b
    inner join TableC c on A. id = c.ID 
    inner join TableD d on D.Typeid = c.Typeid 
    where 
        a.date between @Begindate and @Enddate 
        and a.type in(1, 2, 3); 

Using join:

select
    a.Name, a.id, a.Type, a.date,
    b.pac, a.ConName 
    from TableA a inner join
    (
        select 
            Taskgenid, formgenid, 
            count(pac) pac from TableB
            group by Taskgenid, formgenid
    ) b
        on a. Taskgenid = b.Taskgenid and a.formgenid = b.formgenid
    inner join TableC c on A. id = c.ID 
    inner join TableD d on D.Typeid = c.Typeid 
    where 
        a.date between @Begindate and @Enddate 
        and a.type in(1, 2, 3);

Cross Apply looks simpler, but it will only work if the compatability level of your database is native to SQL Server 2005 (level 90).

Hope this helps.

Oleg
more ▼

answered Jan 10, 2011 at 01:54 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

(comments are locked)
10|1200 characters needed characters left

Try this:

WITH CTE AS
(
   SELECT a.id, COUNT(b.pac) AS cnt
     FROM TableA AS a
          INNER JOIN
          TableB AS b
          ON a.Taskgenid = b.Taskgenid
             AND a.formgenid = b.formgenid
          INNER JOIN
          TableC AS c
          ON A.id = c.ID
          INNER JOIN
          TableD AS d
          ON D.Typeid = c.Typeid
    WHERE a.date BETWEEN @Begindate AND @Enddate
      AND a.type IN(1, 2, 3)
    GROUP BY a.id
)
SELECT a.Name, a.id, a.Type, a.date, b.cnt, a.ConName
  FROM TableA AS a
       INNER JOIN
       CTE AS b
ON b.id = a.id
more ▼

answered Jan 10, 2011 at 01:57 PM

malpashaa gravatar image

malpashaa
404 3

(comments are locked)
10|1200 characters needed characters left

I am not too sure what you mean here, if you just want the fields and not the aggregate, why not just remove the aggregate? The group by will ensure that you don't get duplicates and all is well.

You can of course, be totally OTT and pack your query inside a common table expression (CTE):

;WITH QueryWithAggregate
          AS (SELECT    a.Name,
                        a.id,
                        a.Type,
                        a.date,
                        COUNT(b.pac),
                        a.ConName
              FROM      TableA a
              JOIN      TableB b ON a.Taskgenid = b.Taskgenid
                                    AND a.formgenid = b.formgenid
              JOIN      TableC c ON A.id = c.ID
              JOIN      TableD d ON D.Typeid = c.Typeid
              WHERE     a.date BETWEEN @Begindate AND @Enddate
                        AND a.type IN (1, 2, 3)
              GROUP BY  a.Name,
                        a.id,
                        a.Type,
                        a.date,
                        a.ConName)
    SELECT  Name,
            id,
            Type,
            date,
            ConName
    FROM    QueryWithAggregate
This is of course rather silly, as the aggregate needn't be done if you don't need it.
more ▼

answered Jan 10, 2011 at 01:44 PM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

@WilliamD It looks like the question was about how to simplify the group by because it involves too many columns. The group by is needed though because there are probably multiple matches in TableB for each record in TableA, so those need to be collapsed into a single record. I think that the cross apply is the simplest way to do it.
Jan 10, 2011 at 01:57 PM Oleg
Ok - everyone can ignore my answer. I totally misunderstood the question.
Jan 10, 2011 at 02:02 PM WilliamD
@WilliamD - I get that feeling many times, most days.... although I prefer to say that I gave the right answer, it was just the wrong question... :)
Jan 10, 2011 at 02:23 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Thanks you guys. Learned a lot and it is working!!!!
more ▼

answered Jan 14, 2011 at 01:17 PM

hanoi gravatar image

hanoi
41 4 4 5

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1943
x985

asked: Jan 10, 2011 at 01:32 PM

Seen: 1426 times

Last Updated: Jan 10, 2011 at 01:43 PM