-3

Source Table

I want write a query which would give two columns :

1> Type of Query 2> Count

This result set should have following structure

enter image description here

Here the 1st column values should be predefined and the count has to be calculated . I want to check the Request column of source table and find specific pattern . If that pattern is found increase the count .

For Example :

If there is a word "greenhopper" found in the request column then that belongs to type GREENHOPPER .

OR

If there is a word "gadgets" found it is of the type DASHBOARD . and so on ...

So I want to analyze the usage of various categories by using the log table .

Hence Finally I can get the amount of usage and after that I can build a pie chart out of it . enter image description here

Rish
  • 447
  • 7
  • 24

2 Answers2

1
SELECT 'Greenhopper' AS TypeOfQuery, COUNT(*) AS Cnt
FROM YourTable
WHERE Request LIKE '%Greenhopper%'
UNION ALL
SELECT 'Dashboard', COUNT(*)
FROM YourTable
WHERE Request LIKE '%gadgets%'
-- And so forth

You said they were predefined right? So you'd have ~10 different statements UNION'd together.

Levi W
  • 805
  • 6
  • 13
1
WITH Requests AS
(
SELECT
CASE
WHEN Request LIKE '%Greenhopper%' THEN 'GreenHopper'
WHEN Request LIKE '%gadgets%' THEN 'Gadgets'
-- and so on
ELSE 'Misc'
END RequestType
FROM YourTable
)
SELECT
RequestType,
COUNT(*) RequesCount
FROM Requests
GROUP BY RequestType
;

No data to test but I believe this approach will perform better as the table will be scanned less times. Performance is never going to be ideal though because of the LIKE and first wild card. This will prevent seeks.

Further explanation of why LIKE does not perform here

Having just re looked at the question you may be able to improve performance further by changing the search string so it only has a wildcard on the right

e.g. LIKE 'GET /rest/gadget%' and so on.

Community
  • 1
  • 1
Ally Reilly
  • 282
  • 3
  • 15
  • Thanks a lot this query works perfectly alrite . But when I use Microsoft Reporting Service to Display in Pie Chart it gives and error ".5pt is not a valid unit designator Valid unit designators are in, mm ,cm,pt,pc" . Please can you help me with this too – Rish Jun 18 '12 at 10:23
  • 1
    Unsure what would be causing this, but I doubt it is related. Perhaps raise it as a new question and it will get more exposure. Just a thought though, is it possible to have .5 of a point? – Ally Reilly Jun 18 '12 at 11:50
  • Even I am confused. Any way Thanks a lot for your help – Rish Jun 18 '12 at 11:57