CODEWITHSUNDEEP
sqlsql-servergroup-by
Ryan Gadsdon
Ryan Gadsdon

Reputation: 2378

GROUP BY with COUNT condition

I have a result set such as:  

  Code      No
   1         *
   1         -
   1         4
   1         
   1

Now i basically want a query that has 2 columns, a count for the total amount and a count for those that dont have numbers.

  Code      No_Number    Total
   1         4             5

Im assuming this needs a group by and a count but how can i do the 2 different counts in a query like this?

This is what i had so far, but i am a bit stuck with the rest of it

 SELECT CODE,NO
 Sum(Case when No IN ('*', '-', '') then 1 else 0 end) as Count

Upvotes: 2

Views: 8429

Answers (2)

russ
russ

Reputation: 624

Well, this took a moment :-), however here it is...I have used a CASE statement to create and populate the No_Number column; the database gives the row in the original table a value of 1 if the original table value is a number or gives it a NULL and discards it from the COUNT if not. Then when it makes the count it is only recognising values which were originally numbers and ignoring everything else..

If the result set is in a table or temp table:

SELECT Code,
       COUNT(CASE WHEN [No] NOT LIKE '[0-9]' THEN 1 ELSE NULL END) AS No_Number,
       COUNT(Code) AS Total
FROM <tablename>
GROUP BY Code

If the result set is the product of a previous query you can use a CTE (Common Table Expression) to arrive at the required result or you could include parts of this code in the earlier query.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269513

I think you basically just need GROUP BY:

SELECT CODE,
       SUM(Case when No IN ('*', '-', '') then 1 else 0 end) as Count,
       COUNT(*) as total
FROM t
GROUP BY CODE;

Upvotes: 11

Related Questions