CODEWITHSUNDEEP
sqlaggregate-functions
Mikhail Sokolov
Mikhail Sokolov

Reputation: 556

One column condition in sql

I have a table:

[letter] [Name]  [status]  [price]
      A    row1       1        11
      A    row1       1        15
      B    row2       2         9
      B    row2       3        23
      B    row2       3        30

And want to select data something like this:

 SELECT letter, Name, 
    COUNT(*),
    CASE WHEN price>10 THEN COUNT(*) ELSE NULL END
    GROUP BY letter, Name

the result is:

 A    row1     2      2 
 B    row2     1      null 
 B    row2     2      2

But I want this format:

 A    row1      2     2 
 B    row2      3     2

Please, help me to modify my query

Upvotes: 0

Views: 50

Answers (3)

kiks73
kiks73

Reputation: 3758

Looking to the other answers, probably this is not the best way, but it will work. The count of the prices over 10 is made with a subquery which has a condition on price > 10 and which is joined to the current TAB record with the alias A for the same letter and name.

SELECT letter, 
       Name, 
       COUNT(*),
       (SELECT COUNT(*) FROM TAB WHERE letter = A.letter and Name = A.Name WHERE price>10)
FROM TAB A
GROUP BY letter, Name

Upvotes: 0

neshkeev
neshkeev

Reputation: 6476

Since nulls are not used in aggregate functions:

SELECT letter
     , name
     , count(*)
     , count(
         case when price > 10 then 1 
         end
       ) 
  FROM t
 GROUP BY letter, name

You were very close.

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

Close. Probably want this instead:

SELECT letter, Name, 
  COUNT(*),
  SUM(CASE WHEN price>10 THEN 1 ELSE 0 END)
FROM TableThatShouldHaveAppearedInTheQuestionInTheFromClause
GROUP BY letter, Name

should work. Assuming that the intention of the fourth column is to return the count of the number of rows, within each group, with a price greater than 10. It's also possible to do this as a COUNT() over a CASE then returns non-NULL and NULL results for the rows that should and should not be counted, but I find the above form easier to quickly reason about.

Upvotes: 6

Related Questions