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
Can you provide some sample records and a desired result you would like to achieve?
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 1Thanks ever so much
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
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.
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
