SQL - Trouble using COUNT query

Question

Good day again to all SQL guru here :)

I have a problem querying my table using COUNT.

This is the data inside of the table (tblstudentoffense)

And this is the RESULT that I want (Let's say that there is a new column after the offenseType that will count the Stealing and Gaming Offense

Is this possible? If possible kindly add an explanation with your provided code. Thanks :)

Please read postgresql-performance then edit your question and provide the missing information. — user330315
– user330315, Commented Feb 23, 2017 at 7:05
@TimBiegeleisen I just hide the name of the owner just a respect... you can see the Student ID all data are the same but difference offenseType. — Mark
– Mark, Commented Feb 23, 2017 at 7:09
Your expected output does not make sense. Which value of offenseFN should be displayed for a given combination of studentID and offenseType? — Tim Biegeleisen
– Tim Biegeleisen, Commented Feb 23, 2017 at 7:10

Mansoor · Accepted Answer · 2017-02-23 07:11:35Z

1

Using subquery to get count and concatenate count with main select statement

SELECT DISTINCT OffenseType + ' ' + CAST(B.Cnt AS VARCHAR) ,  
Othercolumn1,Othercolumn2
FROM Your_tableName A
JOIN 
(
  SELECT COUNT(*) Cnt , StudentId
  FROM Your_tableName
  GROUP BY StudentId 
) B ON B.StudentId = A.StudentId

edited Feb 23, 2017 at 7:11

user330315

answered Feb 23, 2017 at 7:07

Mansoor

4,1921 gold badge19 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tim Biegeleisen Over a year ago

This doesn't match the expected output.

Mark Over a year ago

I'll try this one later. Thanks for the reply.

Hikmat Sijapati · Accepted Answer · 2017-02-23 07:16:09Z

0

Try using CONCAT_WS().The CONCAT_WS() function is used to join two or more strings with a separator.

Note: offensetypeCount is an new column having expected output.

SELECT *,CONCAT_WS(' ',offensetype,count(offenseType) AS offensetypeCount FROM tblstudentoffense GROUP BY offensetype;

edited Feb 23, 2017 at 7:16

answered Feb 23, 2017 at 7:09

Hikmat Sijapati

6,9691 gold badge11 silver badges19 bronze badges

4 Comments

GrabNewTech Over a year ago

Is * in select possible when you have only offensetype field in groupby?

GrabNewTech Over a year ago

I don't think the fields which are not in group by cannot be fetched using select clause

Hikmat Sijapati Over a year ago

@Mark did you try this.

Mark Over a year ago

I'll try this one too later. Thanks for the reply. (I'm busy at the moment)

Tom J Muthirenthi · Accepted Answer · 2017-02-23 07:20:48Z

0

Is this what you are looking for:

 WITH SRC AS  
    (SELECT 'STEALING' OFFENSETYPE,'1' STUDENTID FROM DUAL UNION ALL 
     SELECT 'STEALING' OFFENSETYPE,'1' STUDENTID FROM DUAL UNION ALL
     SELECT 'STEALING' OFFENSETYPE,'1' STUDENTID FROM DUAL UNION ALL
     SELECT 'STEALING' OFFENSETYPE,'1' STUDENTID FROM DUAL UNION ALL
     SELECT 'GAMING' OFFENSETYPE,'1' STUDENTID FROM DUAL UNION ALL)  
     SELECT OFFENSETYPE,STUDENTID,COUNT(*) FROM SRC GROUP BY OFFENSETYPE,STUDENTID;

answered Feb 23, 2017 at 7:20

Tom J Muthirenthi

3,3408 gold badges49 silver badges68 bronze badges

1 Comment

Mark Over a year ago

I'll try this too later. Thanks for the reply.

Trung Duong · Accepted Answer · 2017-02-23 07:32:05Z

0

You could try

SELECT CONCAT(offensetype, ' ', COUNT(offensetype)) AS offenseType, 
       studentid, 
       offenseln, 
       offensefn, 
       offensemn, 
       offensesuffixname 
FROM   tblstudentoffense
GROUP  BY offensetype, 
          studentid, 
          offenseln, 
          offensefn, 
          offensemn, 
          offensesuffixname;

I've created a sample at this link http://rextester.com/WZGW41761.

answered Feb 23, 2017 at 7:32

Trung Duong

3,4752 gold badges10 silver badges10 bronze badges

2 Comments

Mark Over a year ago

I'll try this one too later. Thanks for the reply.

Mark Over a year ago

based on your example. Your code is fit on result that I want :-) to all who gave their answers I will test them all but for now I will use Trung Duong code since I understand a little of his code :-) Thanks

Collectives™ on Stack Overflow

SQL - Trouble using COUNT query

4 Answers 4

2 Comments

4 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

4 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related