Total Count in SQL for CourseList Table

-2

I am having an issue counting the algebra I courses from my table.

I would like to show columns SchoolID Alg1 and leave the Section_Number off, i.e.:

110      14

Here is my code and I have attached an image of my output:

SELECT

  DISTINCT

  cc.SchoolID

 ,e.alg1

 ,cc.Section_Number

FROM

  dbo.CRDC_2017_Course_Enrollments AS e

LEFT JOIN

  PSExtracts.PS_SCS_CC_Export AS cc

    ON

    e.StudentID = cc.StudentID

WHERE

  e.alg1 NOT LIKE 'NULL'

  AND (CONVERT(DATE, cc.DateEnrolled, 120) <= '2017-10-01')

  AND (CONVERT(DATE, cc.DateLeft, 120) >= '2017-10-01')

GROUP BY

  cc.SchoolID

 ,e.alg1

 ,cc.Section_Number;

CourseList

edited Nov 19 '18 at 20:21

Eric Brandt

2,2721624

asked Nov 19 '18 at 17:52

Boltz

Then remove Section_Number from your SELECT column list.
– Ken White
Nov 19 '18 at 18:04

Here are my results from my query but, I only want to show a school id and a count of the different sections per class like School Id = 10 and Class Count = 7 SchoolID alg1 Section_Number 10 Algebra I 08 Hon 1 10 Algebra I 08 Hon 3 10 Algebra I 08 Hon 4 10 Algebra I 08 Hon 7 10 Algebra I 08 Hon 6 10 Algebra I 08 Hon 2 10 Algebra I 08 Hon 5
– Boltz
Nov 19 '18 at 19:22

Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add the tag for the database product you are using postgresql, oracle, db2, sql-server, ...
– a_horse_with_no_name
Nov 19 '18 at 21:25

add a comment |

-2

I am having an issue counting the algebra I courses from my table.

I would like to show columns SchoolID Alg1 and leave the Section_Number off, i.e.:

110      14

Here is my code and I have attached an image of my output:

SELECT

  DISTINCT

  cc.SchoolID

 ,e.alg1

 ,cc.Section_Number

FROM

  dbo.CRDC_2017_Course_Enrollments AS e

LEFT JOIN

  PSExtracts.PS_SCS_CC_Export AS cc

    ON

    e.StudentID = cc.StudentID

WHERE

  e.alg1 NOT LIKE 'NULL'

  AND (CONVERT(DATE, cc.DateEnrolled, 120) <= '2017-10-01')

  AND (CONVERT(DATE, cc.DateLeft, 120) >= '2017-10-01')

GROUP BY

  cc.SchoolID

 ,e.alg1

 ,cc.Section_Number;

CourseList

edited Nov 19 '18 at 20:21

Eric Brandt

2,2721624

asked Nov 19 '18 at 17:52

Boltz

Then remove Section_Number from your SELECT column list.
– Ken White
Nov 19 '18 at 18:04

Here are my results from my query but, I only want to show a school id and a count of the different sections per class like School Id = 10 and Class Count = 7 SchoolID alg1 Section_Number 10 Algebra I 08 Hon 1 10 Algebra I 08 Hon 3 10 Algebra I 08 Hon 4 10 Algebra I 08 Hon 7 10 Algebra I 08 Hon 6 10 Algebra I 08 Hon 2 10 Algebra I 08 Hon 5
– Boltz
Nov 19 '18 at 19:22

Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add the tag for the database product you are using postgresql, oracle, db2, sql-server, ...
– a_horse_with_no_name
Nov 19 '18 at 21:25

add a comment |

-2

I am having an issue counting the algebra I courses from my table.

I would like to show columns SchoolID Alg1 and leave the Section_Number off, i.e.:

110      14

Here is my code and I have attached an image of my output:

SELECT

  DISTINCT

  cc.SchoolID

 ,e.alg1

 ,cc.Section_Number

FROM

  dbo.CRDC_2017_Course_Enrollments AS e

LEFT JOIN

  PSExtracts.PS_SCS_CC_Export AS cc

    ON

    e.StudentID = cc.StudentID

WHERE

  e.alg1 NOT LIKE 'NULL'

  AND (CONVERT(DATE, cc.DateEnrolled, 120) <= '2017-10-01')

  AND (CONVERT(DATE, cc.DateLeft, 120) >= '2017-10-01')

GROUP BY

  cc.SchoolID

 ,e.alg1

 ,cc.Section_Number;

CourseList

edited Nov 19 '18 at 20:21

Eric Brandt

2,2721624

asked Nov 19 '18 at 17:52

Boltz

I am having an issue counting the algebra I courses from my table.

I would like to show columns SchoolID Alg1 and leave the Section_Number off, i.e.:

110      14

Here is my code and I have attached an image of my output:

SELECT

  DISTINCT

  cc.SchoolID

 ,e.alg1

 ,cc.Section_Number

FROM

  dbo.CRDC_2017_Course_Enrollments AS e

LEFT JOIN

  PSExtracts.PS_SCS_CC_Export AS cc

    ON

    e.StudentID = cc.StudentID

WHERE

  e.alg1 NOT LIKE 'NULL'

  AND (CONVERT(DATE, cc.DateEnrolled, 120) <= '2017-10-01')

  AND (CONVERT(DATE, cc.DateLeft, 120) >= '2017-10-01')

GROUP BY

  cc.SchoolID

 ,e.alg1

 ,cc.Section_Number;

CourseList

sql

edited Nov 19 '18 at 20:21

Eric Brandt

2,2721624

asked Nov 19 '18 at 17:52

Boltz

edited Nov 19 '18 at 20:21

Eric Brandt

2,2721624

asked Nov 19 '18 at 17:52

Boltz

edited Nov 19 '18 at 20:21

Eric Brandt

2,2721624

edited Nov 19 '18 at 20:21

Eric Brandt

2,2721624

edited Nov 19 '18 at 20:21

Eric Brandt

2,2721624

asked Nov 19 '18 at 17:52

Boltz

asked Nov 19 '18 at 17:52

Boltz

asked Nov 19 '18 at 17:52

Boltz

Then remove Section_Number from your SELECT column list.
– Ken White
Nov 19 '18 at 18:04

Here are my results from my query but, I only want to show a school id and a count of the different sections per class like School Id = 10 and Class Count = 7 SchoolID alg1 Section_Number 10 Algebra I 08 Hon 1 10 Algebra I 08 Hon 3 10 Algebra I 08 Hon 4 10 Algebra I 08 Hon 7 10 Algebra I 08 Hon 6 10 Algebra I 08 Hon 2 10 Algebra I 08 Hon 5
– Boltz
Nov 19 '18 at 19:22

Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add the tag for the database product you are using postgresql, oracle, db2, sql-server, ...
– a_horse_with_no_name
Nov 19 '18 at 21:25

add a comment |

Then remove Section_Number from your SELECT column list.
– Ken White
Nov 19 '18 at 18:04

Here are my results from my query but, I only want to show a school id and a count of the different sections per class like School Id = 10 and Class Count = 7 SchoolID alg1 Section_Number 10 Algebra I 08 Hon 1 10 Algebra I 08 Hon 3 10 Algebra I 08 Hon 4 10 Algebra I 08 Hon 7 10 Algebra I 08 Hon 6 10 Algebra I 08 Hon 2 10 Algebra I 08 Hon 5
– Boltz
Nov 19 '18 at 19:22

Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add the tag for the database product you are using postgresql, oracle, db2, sql-server, ...
– a_horse_with_no_name
Nov 19 '18 at 21:25

Then remove Section_Number from your SELECT column list.
– Ken White
Nov 19 '18 at 18:04

Here are my results from my query but, I only want to show a school id and a count of the different sections per class like School Id = 10 and Class Count = 7 SchoolID alg1 Section_Number 10 Algebra I 08 Hon 1 10 Algebra I 08 Hon 3 10 Algebra I 08 Hon 4 10 Algebra I 08 Hon 7 10 Algebra I 08 Hon 6 10 Algebra I 08 Hon 2 10 Algebra I 08 Hon 5
– Boltz
Nov 19 '18 at 19:22

Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add the tag for the database product you are using postgresql, oracle, db2, sql-server, ...
– a_horse_with_no_name
Nov 19 '18 at 21:25

add a comment |

1 Answer
1

active

oldest

votes

Firstly, your criteria for excluding null values:

e.alg1 not like 'NULL'

Should be:

e.alg1 is not null

As a null value is not equal to the string 'null'

The rest of your questions isn't entirely clear, but perhaps the following is what you are looking to achieve:

select cc.SchoolID, Count(e.alg1)

from 

    dbo.CRDC_2017_Course_Enrollments e left join PSExtracts.PS_SCS_CC_Export cc

    on e.StudentID = cc.StudentID 

where

    e.alg1 is not null

    and 

    (CONVERT(date, cc.DateEnrolled, 120) <= '2017-10-01') AND 

    (CONVERT(date, cc.DateLeft, 120) >= '2017-10-01') 

group by    

    cc.SchoolID

answered Nov 19 '18 at 18:06

Lee Mac

3,60531339

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53380160%2ftotal-count-in-sql-for-courselist-table%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

Firstly, your criteria for excluding null values:

e.alg1 not like 'NULL'

Should be:

e.alg1 is not null

As a null value is not equal to the string 'null'

The rest of your questions isn't entirely clear, but perhaps the following is what you are looking to achieve:

select cc.SchoolID, Count(e.alg1)

from 

    dbo.CRDC_2017_Course_Enrollments e left join PSExtracts.PS_SCS_CC_Export cc

    on e.StudentID = cc.StudentID 

where

    e.alg1 is not null

    and 

    (CONVERT(date, cc.DateEnrolled, 120) <= '2017-10-01') AND 

    (CONVERT(date, cc.DateLeft, 120) >= '2017-10-01') 

group by    

    cc.SchoolID

answered Nov 19 '18 at 18:06

Lee Mac

3,60531339

add a comment |

Firstly, your criteria for excluding null values:

e.alg1 not like 'NULL'

Should be:

e.alg1 is not null

As a null value is not equal to the string 'null'

The rest of your questions isn't entirely clear, but perhaps the following is what you are looking to achieve:

select cc.SchoolID, Count(e.alg1)

from 

    dbo.CRDC_2017_Course_Enrollments e left join PSExtracts.PS_SCS_CC_Export cc

    on e.StudentID = cc.StudentID 

where

    e.alg1 is not null

    and 

    (CONVERT(date, cc.DateEnrolled, 120) <= '2017-10-01') AND 

    (CONVERT(date, cc.DateLeft, 120) >= '2017-10-01') 

group by    

    cc.SchoolID

answered Nov 19 '18 at 18:06

Lee Mac

3,60531339

add a comment |

Firstly, your criteria for excluding null values:

e.alg1 not like 'NULL'

Should be:

e.alg1 is not null

As a null value is not equal to the string 'null'

The rest of your questions isn't entirely clear, but perhaps the following is what you are looking to achieve:

select cc.SchoolID, Count(e.alg1)

from 

    dbo.CRDC_2017_Course_Enrollments e left join PSExtracts.PS_SCS_CC_Export cc

    on e.StudentID = cc.StudentID 

where

    e.alg1 is not null

    and 

    (CONVERT(date, cc.DateEnrolled, 120) <= '2017-10-01') AND 

    (CONVERT(date, cc.DateLeft, 120) >= '2017-10-01') 

group by    

    cc.SchoolID

answered Nov 19 '18 at 18:06

Lee Mac

3,60531339

Firstly, your criteria for excluding null values:

e.alg1 not like 'NULL'

Should be:

e.alg1 is not null

As a null value is not equal to the string 'null'

The rest of your questions isn't entirely clear, but perhaps the following is what you are looking to achieve:

select cc.SchoolID, Count(e.alg1)

from 

    dbo.CRDC_2017_Course_Enrollments e left join PSExtracts.PS_SCS_CC_Export cc

    on e.StudentID = cc.StudentID 

where

    e.alg1 is not null

    and 

    (CONVERT(date, cc.DateEnrolled, 120) <= '2017-10-01') AND 

    (CONVERT(date, cc.DateLeft, 120) >= '2017-10-01') 

group by    

    cc.SchoolID

answered Nov 19 '18 at 18:06

Lee Mac

3,60531339

answered Nov 19 '18 at 18:06

Lee Mac

3,60531339

answered Nov 19 '18 at 18:06

Lee Mac

3,60531339

answered Nov 19 '18 at 18:06

Lee Mac

3,60531339

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu