Jim's Blog Ramblings about novels, comics, programming, and other geek topics

1. 

   Yitzchok
   July 6th, 2008 - 20:09

   Nice post.

   ( REPLY )
2. 

   Ismail Mayat
   July 8th, 2008 - 02:54

   excellent cant wait to start using subsonic 2.1

   ( REPLY )
3. 

   smallolo
   December 1st, 2008 - 23:33

   Year Month Day (count)

   2008 11 25 14
   2008 11 1 32
   2007 12 25 13
   2007 12 20 55
   2007 11 11 11

   —————-
   first:
   SELECT Year,sum([count]) as countY
   FROM [Table]
   group by [Year]

   ————Result

   2007 79
   2008 46

   ===================
   second :
   how can i do return the same result use the subsonic query mothod

   thank you

   ( REPLY )
4. 

   Jim
   December 2nd, 2008 - 07:50

   You would need to do something like:

   SubSonic.SqlQuery qry = new SubSonic.Select(
   SubSonic.Aggregate.GroupBy(
   Table.Columns.Year, “Year”),
   SubSonic.Aggregate.Sum(
   Table.Columns.Count, “Count”)).
   From(Table.Schema)

   You just need to use Sum instead of Count. The Sum function will add up all of the values that are grouped by the year whereas the Count function just counts the number of rows.

   I didn’t test the above code, so I’m not 100% sure it works. You should look into the Sum function if you are trying to add up the counts and then group by the year.

   ( REPLY )
5. 

   Mark Brittingham
   January 17th, 2009 - 20:25

   Thanks – that cleared up an issue I was having!

   ( REPLY )
6. 

   Fatau
   March 31st, 2009 - 04:50

   Great post. But how could i do this query using Subsonic.SqlQuery:
   SELECT news_NewsList.Id, news_NewsList.UserId, (SELECT COUNT(*) AS CommentsCount FROM news_Comments WHERE (NewsListId = news_NewsList.Id)) AS CommentsCount FROM news_NewsList

   Thanks

   ( REPLY )
7. 

   Jim
   March 31st, 2009 - 18:01

   Fatau,

   Personally, if you were going to do stuff like that then I’d just create a view in your database and query the view. You could probably do that, but it wouldn’t be easy since your doing a sub query of a different table within a query of another table.

   ( REPLY )
8. 

   nrk
   May 9th, 2009 - 01:48

   Hi,
   How can I dot the following query in subsonic 2.2

   select Table1.Id, Table1.Name, Table1.Age from Table1
   where Table1.Id =
   (
   select Max(T.Id) from Table1 T
   Where T.Age = 20
   )

   Thanks.
   nRk

   ( REPLY )
9. 

   John
   August 19th, 2009 - 13:48

   Thanks, Jim, for the post.

   Am I on crack, or is this blog entry the only documentation for SubSonic Aggregates?

   Anyway, sorry to bother you, but, I can’t get this to work:

   SubSonic.SqlQuery qry = new SubSonic.Select(
   SubSonic.Aggregate.GroupBy(
   Quote.Columns.CompanyId, “CompanyId”),
   SubSonic.Aggregate.Sum(
   Quote.Columns.QuoteGrandTotal, “SUM”)).
   From(Quote.Schema);

   It results in :

   SELECT
   FROM [dbo].[conn_quote]
   GROUP BY company_id

   ( REPLY )
10. 

    John
    August 19th, 2009 - 14:08

    Jim,

    I got this to work, but, isn’t there a less-hacky way?

    SubSonic.SqlQuery query = new SubSonic.Select(“company_id, SUM(quote_grand_total) AS Expr1″)
    .From(Quote.Schema);
    List aggs = new List();
    aggs.Add(new Aggregate(Quote.Columns.CompanyId, AggregateFunction.GroupBy));

    query.Aggregates = aggs;

    Also, how did your DNA test go?

    -John

    ( REPLY )
11. 

    Jim
    August 19th, 2009 - 16:19

    Are you using SubSonic 3.0 or 2.x? I don’t know if any of this will work with 3.0.

    If you’re using 2.x, then .. this is a wild guess… , but try changing your Sum output field to a name other than “Sum” (in your first post). I think that’s a reserved word in SQL Server (and probably some other dbms).

    SubSonic.SqlQuery qry = new SubSonic.Select(
    SubSonic.Aggregate.GroupBy(
    Quote.Columns.CompanyId, “CompanyId”),
    SubSonic.Aggregate.Sum(
    Quote.Columns.QuoteGrandTotal, “GrandTotal”)).
    From(Quote.Schema);

    ( REPLY )
12. 

    Iftikhar Younus
    March 7th, 2012 - 08:32

    Hi ! i need a simple code to select data from sql and after storing it,wanted to show the result on the grid. regards

    ( REPLY )

Leave a Reply Cancel reply