Post reply

hi, i'm a noob on sql 2008 could anyone suggest me on how to do it

  • September 4, 2015 at 6:46 am

    #311399

    I have a table with these fields:

    student number,gender,subject,date

    now, upon enrolling it will store 5 or more records into the db with the same values for student number,gender and date with

    the exeption of subject...

    my problem is to how to group these records/rows into one through the gender and date..

    any help i really appreciate.

    thanks

  • September 4, 2015 at 6:50 am

    #1824616

    Can you please add more information to your question?

    See http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ for guidelines.

    -- Gianluca Sartori

  • September 4, 2015 at 6:51 am

    #1824618

    dlanorfeb24 (9/4/2015)

    I have a table with these fields:

    student number,gender,subject,date

    now, upon enrolling it will store 5 or more records into the db with the same values for student number,gender and date with

    the exeption of subject...

    my problem is to how to group these records/rows into one through the gender and date..

    any help i really appreciate.

    thanks

    Hi

    It would be easier if you provide the DDL (CREATE TABLE and INSERT commands) plus a sample of the desired output, but perhaps the DISTINCT keyword is what you want:

    SELECT DISTINCT

    STUDENT_NO, GENDER, DATE

    FROM STUDENT_TABLE

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • September 4, 2015 at 7:05 am

    #1824625

    If you're really on SQL 2008, then a SQL 2014 specific forum is probably not the best place for you to get answers. There is a forum specifically for SQL Server Newbies.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • September 4, 2015 at 12:01 pm

    #1824753

    its like this..

    student number name gender subject year

    201500001 peter male english 2015

    201500001 peter male science 2015

    201500001 peter male biology 2015

    201500002 ann female english 2015

    201500002 ann female science 2015

    201500002 ann female biology 2015

    ....and so on

    now,since 201500001 is owned by peter which is common to the other rows with the exemption of subjects...i'm going to use this one

    as a demograph for my project

    gender total year

    male 1 2015

    female 1 2015

    thanks in advance

  • September 4, 2015 at 12:14 pm

    #1824756

    Help us help you!

    if you can provide your sample data in a consumable format(meaning copy and paste to SSMS and build the data)

    as well as the queries you are running and having trouble with, we can help out.

    we won't actually do homework, but will certainly give you some awesome peer review and suggestions..

    here's a sample table i built, based on what you posted.

    how does that match or not to your table?

    CREATE TABLE #ExampleData([studentnumber] varchar(30),[name] varchar(30),[gender] varchar(30),[subject] varchar(30),[year] int)

    INSERT INTO #ExampleData

    SELECT '201500001','peter','male','english','2015' UNION ALL

    SELECT '201500001','peter','male','science','2015' UNION ALL

    SELECT '201500001','peter','male','biology','2015' UNION ALL

    SELECT '201500002','ann','female','english','2015' UNION ALL

    SELECT '201500002','ann','female','science','2015' UNION ALL

    SELECT '201500002','ann','female','biology','2015'

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 4, 2015 at 12:18 pm

    #1824757

    The biggest challenge you are going to have here is getting reliable data back out. This is because your data is not normalized. This should be at least three tables (Students, Subjects, StudentSubjects). If you keep everything in a denormalized table it is going to be challenging to get the data back correctly and easily.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • September 4, 2015 at 12:19 pm

    #1824758

    You should read about aggregate functions. There are many places to read, but you can get a quick start by using the Stairway to T-SQL DML on this site[/url].

    Your query would look something like this:

    SELECT gender,

    COUNT(DISTINCT student) total, --Use student as different students can share the name but not student id

    year

    FROM YourTable

    GROUP BY gender, year

    Try to read something about data normalization[/url] which might complicate things on the beginning, but you'll be grateful when things start to get serious.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • September 5, 2015 at 4:21 am

    #1824819

    Hi,

    I really appreciate your quick reply.

    Please I attach a file for a table that i am referring to. sorry if i couldn't understand some of the terminology that others used.

    but this is the only thing that i can share just to understand my point. And, i am very thankful for the others advise and links shared

    for me to start my learning process on SQL.

    Thank you and God speed all.

  • September 5, 2015 at 4:58 am

    #1824820

    Luis Cazares,

    Thank you for your suggestion. And thank you all for you immediate respond.

Viewing 10 posts - 1 through 9 (of 9 total)

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