question

tabularyee avatar image
tabularyee asked

multiple column grouping

I have two tables table 1 id file owner string1 string2 and table 2 id file owner date type table 2 has all the time table1.file has appeared along with its language I want to see how many times an file in table 1 is appearing in table 2 and group it by file and language but also show the additional information (string1 and string2 in the result). Would it be something like this Select t1.id, t1.file, t1.owner, COUNT(t2.file) as [File Count], t1.string1, t1.string2 FROM t1 LEFT OUTER JOIN t2 ON t2.type= 3 GROUP BY t1.file, t1.owner Thanks
sql-server-2005sqlgroup-by
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you provide some sample records and a desired result you would like to achieve?
1 Like 1 ·
This is how I would like to see the data
table 1 would have the instances

i.e

1 file1  fred
2 file2  fred
3 file3  fred
4 file1  carmen
5 file2  carmen
6 file3  carmen

and table two will list all the occorences of those files

1 file2 fred
2 file2 fred
3 file1 carmen
4 file2 carmen
5 file3 carmen


and my results table would be

id file  owner count string1 string2
1  file1 fred   0      
2  file2 fred   2 
3  file3 fred   0
4  file1 carmen 1
Thanks ever so much
0 Likes 0 ·
Oleg avatar image
Oleg answered
You are almost there. The only thing you are missing is the correct condition of your join. Without joining your tables correctly, you get a cartesian product. Also, if you need to see the owner, string1 and string2 of table1 then these must be either included in the group by or else their aggregates need to be selected, not the actual values. Try this: select t1.id, t1.file, t1.owner, count(1) [File Count], t1.string1, t1.string2 from t1 left join t2 on t1.file = t2.file and t2.[type] = 3 group by t1.id, t1.file, t1.owner, t1.string1, t1.string2; If you have data in your tables such that for each record in t1 you have zero to many records in t2 for the same file then the query above should give you results you want. Oleg
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
As your query stands, you would have to put each item of the SELECT list that's not the aggregate field (ie the field returned by COUNT) within the GROUP BY statement. However, there may be a way around that, depending on your exact requirement. If you can update with an answer to @Pavel's comment, that would help us to figure out more precisely what you're looking for.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
As far as I understand it you are wanting a count of entries in @Table2 that match @Table1 on file and owner: DECLARE @Table1 AS TABLE (id int, [file] int, [owner] int, string1 varchar(100), string2 varchar(100)) INSERT INTO @Table1 SELECT 1,1,1,'String1','String2' UNION ALL SELECT 2,2,1,'String1','String2' DECLARE @Table2 AS TABLE (id int, [file] int, [owner] int, [date] date, [type] tinyint) INSERT INTO @Table2 SELECT 1,1,1,'2011-02-20',1 UNION ALL SELECT 2,1,1,'2011-02-21',2 UNION ALL SELECT 3,2,1,'2011-02-20',1 SELECT t1.[file],t1.[owner],t1.string1,t1.string2,COUNT(t2.[file]) FROM @table1 t1 INNER JOIN @table2 t2 ON t1.[file] = t2.[file] AND t1.[owner] = t2.[owner] GROUP BY t1.[file],t1.[owner],t1.string1,t1.string2
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.