Group By, need help guilding a query

[dupuis2387]

Hello


I need to figure out to group the results of a query (select * from table1 a where not exists (select * from table2 b where
a.columname = b.columname)

That query returns 192 rows, now those rows all trace back to a SourceFile file in the SourceFile column, certain rows draw from the same SourceFile in the SourceFile column. What i need to do is create a query that will take the result of the query specified above and create two columns that says these many rows (records) draw back from this SourceFile.

EX:
SourceFile|Records
==========|========
File1 |12 rows belong to this file(from the 1st query)
File2 | 5 rows belong to this file(" ")


etc...

I know I need to use the group by query, but i just can't figure out the right syntax.



Help?

 

[r937]

could you show how they "draw back from the SourceFile"

is that another join, perhaps? what columns are involved?

r937.com | rudy.ca

 

[dupuis2387]

well like I said there's a column that called SourceFile, from the query I typed above, I get X-number of rows, I want to group that number of rows and categorize them based on the SourceFile column. So several rows will have the same SourceFile value, I want to group the rows, based on the commonality of having the same value in the SourceFile column.

Column1|Column2|Column3|Column4|Column5|SourceFile
--------------------------------------------------
blah |blah |blah |blah |blah | File1
--------------------------------------------------
blah |blah |blah |blah |blah | File1
--------------------------------------------------
blah |blah |blah |blah |blah | File2
--------------------------------------------------
blah |blah |blah |blah |blah | File3
--------------------------------------------------
blah |blah |blah |blah |blah | File3
--------------------------------------------------
blah |blah |blah |blah |blah | File3
--------------------------------------------------


What I want to get is:

Number_of_Rows|SourceFile
-------------------------
2 |File1
-------------------------
1 |File2
-------------------------
3 |File3


Its summary grouping the rows based on a common sourcefile.

I hoep that clears things up.

 

[mutley1]

If it's only a count of the occurences of each sourcefile from that column, then

select distinct sourcefile, count (*)
from mytable
group by sourcefile

 

[r937]

select count(*) as Number_of_Rows
     , SourceFile
  from table1 a 
 where not exists 
     ( select * 
         from table2 b 
        where a.columname = b.columname )
group
    by SourceFile

r937.com | rudy.ca

 

[dupuis2387]

r937 that's it! thanks much, i was COUNT-ing the wrong thing. But again, thanks much for the help!