CODEWITHSUNDEEP
sqlcountdb2
Ardia
Ardia

Reputation: 89

show a SQL select along with a count of itself?

(Caution: Newbie alert) I have to put a count of the subsections of a generated view along with the view itself.

Is it possible?

For example, this is the generated view from a SQL select:

Client Type      Year
8963   Rural     2012
9044   City      2013
8963   Rural     2014
5145   Rural     2014
5145   City      2012

What I want displayed is:

Client Type      Year  CountofRural2012 CountofCity2012 CountofRural2013
8963   Rural     2012  1                   1                  0
9044   City      2013
8963   Rural     2014
5145   Rural     2014
5145   City      2012

...and so on for all the count permutations.

Upvotes: 1

Views: 330

Answers (3)

Coder221
Coder221

Reputation: 1433

Ardia,

Here is the way to do it without creating a new table

select [Client], [Type], [Year],
(Select COUNT([Year]) From PTable  where [Year] = 2012 and [Type] = 'Rural') [CountofRural2012],
(Select COUNT([Year]) From PTable  where [Year] = 2012 and [Type] = 'City') [CountofCity2012],
(Select COUNT([Year]) From PTable  where [Year] = 2013 and [Type] = 'Rural')[CountofRural2013]
from PTable

Here is the live demo https://data.stackexchange.com/stackoverflow/query/397150

Hope it helps.

Just update manually like this after you run above query

  Update PTable Set [CountofRural2012] = '' where Client <> 8963
  Update PTable Set [CountofCity2012] = '' where Client <> 8963
  Update PTable Set [CountofRural2013] = '' where Client <> 8963

Upvotes: 0

DougieHauser
DougieHauser

Reputation: 470

First of all, I'd advise you to look into functions such as pivot.

https://www.ibm.com/developerworks/community/blogs/SQLTips4DB2LUW/entry/pivoting_tables56?lang=en

Secondly, if the fields you want to create are known in advance (meaning, you want to create fields for specific years), you can try to sum/count a case field.

Select client, type, year,
       sum(case when type='Rural' and year=2012 then 1 else 0 end) countOfRural2012
from YOUR_TABLE
Group by client, type, year

Upvotes: 1

user2858650
user2858650

Reputation:

Look into Subqueries and the Count function. You can wrap a query in another query and use aggregates to count, sum or even do standard deviations on the results.

Upvotes: 0

Related Questions