Professional Web Applications Themes

Group by query - Microsoft SQL / MS SQL Server

Create table #test (Name varchar(30), Value int) Insert #test values('exec sproc 1 ',1) Insert #test values('exec sproc 2,2 ',1) Insert #test values('exec sproc 3',1) Insert #test values('exec sproc 4,1 ',1) Insert #test values('exec storedproc 1',1) Insert #test values('exec storedproc 3,4' ,1) Insert #test values('exec dbo.storedprocedure 1 ',1) Desired output Name Count exec sproc 4 exec storedproc 2 exec dbo.storedprocedure 1 I seem to be struggling at the group by portion cos i want to figure out the number of times the stored procedure was called irrespective of the parameters.This is just an example from the profiler output thats saved into ...

  1. #1

    Default Group by query

    Create table #test
    (Name varchar(30),
    Value int)

    Insert #test values('exec sproc 1 ',1)
    Insert #test values('exec sproc 2,2 ',1)
    Insert #test values('exec sproc 3',1)
    Insert #test values('exec sproc 4,1 ',1)
    Insert #test values('exec storedproc 1',1)
    Insert #test values('exec storedproc 3,4' ,1)
    Insert #test values('exec dbo.storedprocedure 1 ',1)

    Desired output

    Name Count
    exec sproc 4
    exec storedproc 2
    exec dbo.storedprocedure 1

    I seem to be struggling at the group by portion cos i want to figure out the
    number of times the stored procedure was called irrespective of the
    parameters.This is just an example from the profiler output thats saved into
    a trace table and I want to check how many times the SPs were called for
    ysis


    Hassan Guest

  2. #2

    Default Re: Group by query

    Hello Hassan !

    One example where you see that, when you separate values in a database, it
    is much easier to handle with. If you would have two column, one for the
    command and one for the values given you should run the query in a much
    easier way.

    Nethertheless, this query will workaround the problem:


    Select 'exec sproc' AS Name, count(PATINDEX('exec sproc%',name))
    from #test where PATINDEX('exec sproc%',name)>0
    UNION
    Select 'exec storedproc' AS Name, count(PATINDEX('exec storedproc%',name))
    from #test where PATINDEX('exec storedproc%',name)>0
    UNION
    Select 'exec dbo.storedprocedure' AS Name, count(PATINDEX('exec
    dbo.storedprocedure%',name))
    from #test where PATINDEX('exec dbo.storedprocedure%',name)>0



    HTH, Jens Süßmeyer.


    Jens Guest

  3. #3

    Default Re: Group by query

    So, try to solve the problem by design. Insert the values and the command in
    separate columns. Or store only the command in a separate table to be able
    to join the table and count the hits.

    If you can´t predefine the commands executed/inserted, how should you be
    able to define a pattern to search for ?

    Jens Süßmeyer.



    Jens Guest

  4. #4

    Default Re: Group by query

    This is for ysis of the profiler textdata column when saved in a trace
    table. I want to group by stored procedure name so I can get avg duration,
    avg CPU, avg reads for the stored procedures ignoring the parameters


    "Jens Süßmeyer" <jsuessmeyer[REJECT_SPAM]web.de> wrote in message
    news:phx.gbl... 
    in 


    Hassan Guest

  5. #5

    Default Re: Group by query

    Hassan
    Search on this site for 'load test'. http://vyaskn.tripod.com/
    There are a lot of examples how to find long running sp and etc.





    "Hassan" <com> wrote in message
    news:phx.gbl... [/ref]
    command [/ref]
    able 
    >
    >[/ref]


    Uri Guest

  6. #6

    Default Re: Group by query

    You need to define a rule for where the proc name ends and where the parameters starts. If you can'r
    express that in words, then it is very diffucult to implement it in code/logic. Say that we devive
    that the second space denotes the end for the proc name:


    SELECT Name, COUNT(*) FROM
    (
    SELECT LEFT(Name, CHARINDEX(' ', Name, 6)) AS Name, Value
    FROM #test
    ) AS t
    GROUP BY Name

    Using a derived table, I don't have to repeat the expression. The expression uses charindex to find
    the first space after te 6:th character (assumes EXEC) and feeds that to SUBSTRING.

    --
    Tibor Karaszi, SQL Server MVP
    Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver


    "Hassan" <com> wrote in message news:phx.gbl... 
    > in 
    >
    >[/ref]


    Tibor Guest

Similar Threads

  1. Query Using Group Problem
    By rpmindllc in forum Coldfusion Database Access
    Replies: 17
    Last Post: August 20th, 08:29 PM
  2. Help with simple GROUP BY query...
    By Aetherweb in forum MySQL
    Replies: 3
    Last Post: March 8th, 05:12 PM
  3. cfgrid inside a <cfoutput query="myQuery" group="GROUP">
    By DavidGhous in forum Coldfusion Flash Integration
    Replies: 1
    Last Post: April 12th, 07:23 PM
  4. NDS user and group query
    By Michael Weber in forum PERL Beginners
    Replies: 2
    Last Post: February 13th, 10:48 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139