How to count words from a column.

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

Hello All

Am new to this SQL.
How to count words in a column and sum it as a total.

Example:
Table1 contains

State  ||  crs1  || crs2  ||  crs3  || Capacity  ||  crs1_tar  || crs2_tar  ||  crs3_tar
AP     ||  25    ||  10   ||  0     ||  360      ||  Approved  || No        || No
TS     ||  10    ||  20   ||  2     ||  360      ||  no        || Approved  || Approved
MP     ||  21    ||  33   ||  2     ||  360      ||  no        ||  no       ||  no

Here crs1,2,3 is Course names and crs_tar is targets for the course.

Well, i need a consolidated data like the following:

TS        ||  crs1  ||  crs2  ||  crs3
Capacity  ||  0     ||  360   ||  360
Allocated ||  10    ||  20    ||  2

Here Capacity is counted according to crs_tar.

I hope am clear.

What I have tried:

I have tried it in the excel sheet and need to convert it to SQL

Posted 22-Feb-16 5:31am

Member 10376341

Updated 22-Feb-16 6:34am

CHill60

v5

Add a Solution

Comments

CHill60 22-Feb-16 11:41am

The "What I have tried" section is for you to put in the code you have already tried.
I am struggling to see how you have got to your "consolidated" data at all

Member 10376341 22-Feb-16 12:26pm

Am sorry, i have tried it in the MS-Excel sheet and i have used SUMIFS() function with sum range and criteria range.

Maciej Los 22-Feb-16 12:49pm

Based on what logic do you want to transpose rows to columns?
I have no idea how Capacity for crs1 become to zero. The same doubt for Allocated...

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 3257606 · Accepted Answer · 2016-02-22T06:34:00

It is difficult to quite get what you are trying to do. But it seems that you need to start by designing a more normalized database.

What are the entities (things) described?

(Courses would seem to be one, "State" may be another but that is not clear, and what is a "Target"?).

What does "Capacity" describe? Is it an attribute of a "State" or an attribute of a Course, or does it refer to something else? And what does it mean that a "Target" is Approved?

My guess is also that in a real world scenario, you would want to define the INSTANCES of a course as separate from the course description, so I'm guessing maybe this is a classroom exercise?

Represent each separate entity in a separate table, and show how they relate to each other.

Something like (just guess on what your intentions are):

Table: States

Columns:  StateID, StateName, Capacity
(1, 'AP', 360),
(2, 'TS', 360),
(3, 'MP', 360)

Table: Courses

Columns:  CourseID, CourseName, ...
(101, 'crs1', ...),
(102, 'crs2', ...),
(103, 'crs3', ...)

Intermediate table ties Courses and States together:

Table: StatesCourses

Columns:  StatesCoursesID, StateID, CourseID, Allocation, Approved
(1001, 1, 101, 25, 'Y'),
(1002, 1, 102, 10, 'N'),
(1003, 1, 103, 0, 'N'),
(1004, 2, 101, 10, 'N'),
(1005, 2, 102, 20, 'Y'),
(1006, 2, 103, 2, 'Y'),
(1007, 3, 101, 21, 'N'),
(1008, 3, 102, 33, 'N'),
(1009, 3, 103, 2, 'N')

Now you can get the following result showing allocated by course and state: State, Capacity, Course, Allocated using a query like this:

SQL

SELECT State, Capacity, Course, Allocated
FROM States as S
INNER JOIN StatesCourses as SC
ON SC.StateID = S.StateID
INNER JOIN Courses as C
ON C.CourseID = SC.CourseID

In order to get the specific layout you are looking for, you can either construct a custom query that only works for the currently known courses and states (not a very flexible solution), or you can look into PIVOT (or TRANSFORM) in your database's Help.

Until you get a handle on the relational data design, you may be better off sticking with keeping your data in a spreadsheet. That isn't necessarily a bad thing. Databases are great when you have a lot of data or it gets difficult to manage and you need more flexibility. But to get that flexibility, you need to have a reasonable design of the database. Search the web for Introduction to Relational Databases for additional help

HTH.