Create New Table - Count Columns

Question

Post reply

Create New Table - Count Columns

HenPBIPatron

SSC Veteran

Points: 270
More actions
August 10, 2021 at 10:41 am
Go to Answer
#3918397

Hi all,

I am new to writing SQL code and am wondering the best way to create a new table from an existing table that has count columns as shown below

Existing Table:
[candidateref]
[candidate postcode]

Required Table

Thank you,

HPBI

ktflash

Default port

Points: 1400

August 10, 2021 at 11:18 am

Answer

#3918404

Depends on what ur looking for, are you looking for a count or a distinct count (counting each ref only once, ignoring duplicates)

Maybe something like this case when helps

select [candidateref]
      ,[candidate postcode]
  --Count
  ,sum(case when POSTCODE IS NULL
 THEN  1
 else 0 END)
  ,sum(case when POSTCODE IS NOT NULL
 THEN  1
 else 0 END)
  --Distinct count, -1 because of the default 'XXXX'
  ,count(distinct 
         case when POSTCODE IS NULL
 THEN  [candidateref]
 else 'XXXX' END)-1
  ,count(distinct 
         case when POSTCODE IS NOT NULL
 THEN  [candidateref]
 else 'XXXX' END)-1

I want to be the very best
Like no one ever was

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

August 11, 2021 at 11:16 am

This was removed by the editor as SPAM
August 11, 2021 at 11:19 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

HenPBIPatron SSC Veteran Points: 270 More actions · Answer 1

I tried below:

select [candidateref],

[candidatepostcode],

Count([candidatepostcode]),sum(case when [candidatepostcode] IS NULL THEN 1 else 0 END) ,

sum(case when [candidatepostcode] IS NOT NULL THEN 1 else 0 END)

from [CandidateGeoLocation]

but received the below error

Msg 8120, Level 16, State 1, Line 1

Column 'CandidateGeoLocation.CandidateRef' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

ktflash Default port Points: 1400 More actions · Answer 2

Every column thats used in select but not an aggregation (avg/sum/...) needs to be in the group by after the from:

group by [candidateref],                 
         [candidatepostcode]?

I want to be the very best
Like no one ever was

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

HenPBIPatron SSC Veteran Points: 270 More actions · Answer 3

Thank you! How do I then create a table for this statement?

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 4

Hi...just wondering why you would want to create a table with just one row from these results?

Also....what do you want to record when the postcode column is just an empty string and not "NULL" ?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day