We help IT Professionals succeed at work.

SQL Count depending on the way the data is presented

Andy Green
Andy Green asked
on
Hi Experts

I have rows of (string) id data for example


123456
789012
*1234632*
*7645281*
*45reartf*

What I'm trying to do is separate the counts for those with * at each end and those without. So for the above I need 2 and 3

Is there a simple way to achieve this please

Andy
Comment
Watch Question

View Solution Only

ste5anSenior Developer
BRONZE EXPERT

Commented:
E.g.

SELECT SUM(IIF(Q.StringData LIKE '%*%', 1, 0)) AS WithAsterisk ,
       SUM(IIF(Q.StringData LIKE '%*%', 0, 1)) AS WithoutAsterisk
FROM   (   VALUES ( '789012' ) ,
                  ( '*45reartf*' ) ,
                  ( '123456' ) ,
                  ( '*1234632*' ) ,
                  ( '*7645281*' )) Q ( StringData );

Open in new window

SQL Server 2012+
Andy Green

Author

Commented:
Thanks - I'm using sql Server 2008. Is this for a later version, to run or psedo code to give an idea?

Andy
Senior Developer
BRONZE EXPERT
Commented:
Replace the IIF using CASE. The row value constructor was imho introduced in 2008 and should work. Something like

SELECT SUM(CASE WHEN Q.StringData LIKE '%*%' THEN 1
                ELSE 0
           END) AS WithAsterisk ,
       SUM(CASE WHEN Q.StringData LIKE '%*%' THEN 0
                ELSE 1
           END) AS WithoutAsterisk
FROM   (   VALUES ( '789012' ) ,
                  ( '*45reartf*' ) ,
                  ( '123456' ) ,
                  ( '*1234632*' ) ,
                  ( '*7645281*' )) Q ( StringData );

Open in new window


btw, SQL Server 2008 is out of support..
Scott PletcherSenior DBA
SILVER EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
SELECT
    SUM(CASE WHEN LEFT(id_string, 1) = '*' AND RIGHT(id_string, 1) = '*' THEN 0 ELSE 1 END) AS count_without_star,
    SUM(CASE WHEN LEFT(id_string, 1) = '*' AND RIGHT(id_string, 1) = '*' THEN 1 ELSE 0 END) AS count_with_star
FROM dbo.table_name
awking00Information Technology Specialist
BRONZE EXPERT

Commented:
>>What I'm trying to do is separate the counts for those with * at each end and those without. So for the above I need 2 and 3<<
If what you want is the count of records starting and ending with an asterisk, then
select sum(case when id like '*%*' then 1 else 0 end) as cnt
from yourtable
By selecting only records 2 and 3, If what you want is the count of records starting and ending with an asterisk with only numbers in between, then
select sum(case when id like '*%*' and id not like '%[A-Za-z]' then 1 else 0 end) as cnt
from yourtable
ste5anSenior Developer
BRONZE EXPERT

Commented:
btw, my pattern counts as the column alias name says rows with or without asterisk. If you want to count exactly for start and end asterisks, then the pattern is '*%*':

SELECT SUM(CASE WHEN Q.StringData LIKE '%*%' THEN 1
                ELSE 0
           END) AS WithAsterisk ,
       SUM(CASE WHEN Q.StringData LIKE '%*%' THEN 0
                ELSE 1
           END) AS WithoutAsterisk ,
       SUM(CASE WHEN Q.StringData LIKE '*%*' THEN 1
                ELSE 0
           END) AS WithStartAndEndAsterisk ,
       SUM(CASE WHEN Q.StringData LIKE '*%*' THEN 0
                ELSE 1
           END) AS WithoutStartAndEndAsterisk
FROM   (   VALUES ( '789012' ) ,
                  ( '*45reartf*' ) ,
                  ( '123456' ) ,
                  ( '*1234632*' ) ,
                  ( '*7645281*' ) ,
                  ( 'Don''t*count*me' ) ,
                  ( '*Neither*me..' ) ,
                  ( '..nor*me*' )) Q ( StringData );

Open in new window

This was essentially a typo in my first post and I didn't notice it prior now.
awking00Information Technology Specialist
BRONZE EXPERT

Commented:
I don't know if there is any difference in performance but the second option could be written as
select sum(case when id like '*%*' and patindex('%[^0-9]%',id) >0 then 1 else 0 end) as cnt
 from yourtable
ste5anSenior Developer
BRONZE EXPERT

Commented:
@awking: What pattern should this catch or exclude?

SELECT SUM(CASE WHEN Q.StringData LIKE '%*%' THEN 1
                ELSE 0
           END) AS WithAsterisk ,
       SUM(CASE WHEN Q.StringData LIKE '%*%' THEN 0
                ELSE 1
           END) AS WithoutAsterisk ,
       SUM(CASE WHEN Q.StringData LIKE '*%*' THEN 1
                ELSE 0
           END) AS WithStartAndEndAsterisk ,
       SUM(CASE WHEN Q.StringData LIKE '*%*' THEN 0
                ELSE 1
           END) AS WithoutStartAndEndAsterisk ,
       SUM(CASE WHEN StringData LIKE '*%*'
                     AND PATINDEX('%[^0-9]%', StringData) > 0 THEN 1
                ELSE 0
           END) AS AwkingCount
FROM   (   VALUES ( '789012' ) ,
                  ( '*45reartf*' ) ,
                  ( '*45re*artf*' ) ,
                  ( '123456' ) ,
                  ( '*1234632*' ) ,
                  ( '*7645281*' ) ,
                  ( 'Don''t*count*me' ) ,
                  ( '*Neither*me..' ) ,
                  ( '..nor*me*' )) Q ( StringData );

Open in new window

Capture.PNG
awking00Information Technology Specialist
BRONZE EXPERT

Commented:
It catches precisely what you would expect. The AwkingCount of 4 was only to catch the count of records starting and ending with an asterisk. What I was trying to say regarding your last post was that only three counts were necessary, asterisks at start and end, no asterisks, and asterisks not at start and end. While it's not real hard to figure out that the WithStartAndStreakAsterisk count is made up of 2 without and 3 with asterisk but not at start and end, it would be more intuitive to show 3 with asterisk but not at start and end, 2 without asterisk, and 4 with asterisk at start and end for a total of 9 which equals the overall total of records. To get the count of records with asterisk but not at start and end, you could use sum(case when charindex('*',Q.StringData) > 1 then 1 else 0 end) as WithAsteriskNotAtStartAndEnd and eliminate the WithoutAtStartAndEndAsterisk calculation.
ste5anSenior Developer
BRONZE EXPERT

Commented:
ahh, my sample last had four values, cause I initially simply mistyped the pattern. I've included them to show the difference. The first two with the wrong pattern for the problem, cause they simple count any asterisk and the second which go for starting/end asterisks only.
Thus just take my first post and change the pattern.
Scott PletcherSenior DBA
SILVER EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
This is all irrelevant to the original q, which was just to count rows that had '*' at each end and those that didn't, period.  
ste5anSenior Developer
BRONZE EXPERT

Commented:
Scott, as I wrote, my first pattern was incorrect. And I've given the four values as demonstration of the difference. Thus it is relevant.
Scott PletcherSenior DBA
SILVER EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
awking did not ask the q.  He appears to have misread the initial q and thus gone off on a tangent.  I fail to see the relevance to the actual q.  Please explain how it's relevant to that.