3 Replies - 422 Views - Last Post: 30 January 2017 - 10:32 AM

#1 marvinmartian  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 42
  • Joined: 07-October 09

Return a single record

Posted 29 January 2017 - 11:40 PM

my code doesn't seem to have an error but its output is not what i wanted.
this sample code below returns the attach sampledata image but what i needed is this
CEA BS ARCH 370 298
SELECT     TOP 100 PERCENT dbo.fn_CollegeCode(dbo.ES_studentinfo.CollegeID) AS College, dbo.fn_ProgramCode(dbo.ES_studentinfo.Campusid, 
                      dbo.ES_studentinfo.Progid) AS ProgramCode, dbo.fn_LogType(dbo.id_student_logs2.Log_Typeidx) AS LogType, 
                      COUNT(DISTINCT dbo.id_student_smartcard.studentno) AS [Count]
FROM         dbo.id_student_logs2 INNER JOIN
                      dbo.id_student_smartcard ON dbo.id_student_logs2.smartcard_idx = dbo.id_student_smartcard.smartcard_idx INNER JOIN
                      dbo.ES_studentinfo ON dbo.id_student_smartcard.studentno = dbo.ES_studentinfo.studentno
WHERE     (dbo.id_student_logs2.datetime_log >= { fn CURDATE() })
GROUP BY dbo.fn_CollegeCode(dbo.ES_studentinfo.CollegeID), dbo.fn_LogType(dbo.id_student_logs2.Log_Typeidx), 
                      dbo.fn_ProgramCode(dbo.ES_studentinfo.Campusid, dbo.ES_studentinfo.Progid), dbo.fn_LogIN(dbo.ES_studentinfo.Progid, 
                      dbo.ES_studentinfo.CollegeID, 1)
ORDER BY dbo.fn_CollegeCode(dbo.ES_studentinfo.CollegeID), dbo.fn_ProgramCode(dbo.ES_studentinfo.Campusid, dbo.ES_studentinfo.Progid)

Attached image(s)

  • Attached Image
  • Attached Image

Is This A Good Question/Topic? 0
  • +

Replies To: Return a single record

#2 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2329
  • View blog
  • Posts: 9,356
  • Joined: 03-December 12

Re: Return a single record

Posted 30 January 2017 - 07:21 AM

TOP is the limit operator in SQL Server, so there is always that if you just want a single record. Using TOP 100 PERCENT, literally means, return all the records. Correct usage would mean to limit it by a percentage, ie, TOP 50 PERCENT means to return half of the result set.
Was This Post Helpful? 0
  • +
  • -

#3 andrewsw  Icon User is offline

  • lashings of ginger beer
  • member icon

Reputation: 6333
  • View blog
  • Posts: 25,530
  • Joined: 12-December 12

Re: Return a single record

Posted 30 January 2017 - 07:27 AM



PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output

Was This Post Helpful? 0
  • +
  • -

#4 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 6966
  • View blog
  • Posts: 14,572
  • Joined: 16-October 07

Re: Return a single record

Posted 30 January 2017 - 10:32 AM

There are built in functions for what is called a pivot table or a crosstab. I never use them. Instead, I just use plain old SQL... mostly because I find the syntax unintuitive and once you know SQL well, you can play all kinds of tricks with just the basics.

First, some test data:
create table #foo([Name] varchar(30), [Thing] varchar(30))
insert #foo values('Alice','Pizza')
insert #foo values('Bob','Burger')
insert #foo values('Chuck','Pizza')
insert #foo values('Deb','Burger')
while ((select count(*) from #foo)<12) begin
  insert #foo select (select top 1 [Name] from #foo order by NEWID()), (select top 1 [Thing] from #foo order by NEWID())

select * from #foo

Name                           Thing
------------------------------ ------------------------------
Alice                          Pizza
Bob                            Burger
Chuck                          Pizza
Deb                            Burger
Alice                          Burger
Chuck                          Pizza
Bob                            Pizza
Alice                          Burger
Deb                            Burger
Deb                            Pizza
Alice                          Burger
Alice                          Pizza

(12 row(s) affected)

You needn't know how the test data generator worked, I just got lazy.

Now we have some data to play with, let's do a group by:
select [Name], [Thing], count(*) as Qty
  from #foo
  group by [Name], [Thing]

Name                           Thing                          Qty
------------------------------ ------------------------------ -----------
Alice                          Burger                         3
Bob                            Burger                         1
Deb                            Burger                         2
Alice                          Pizza                          2
Bob                            Pizza                          1
Chuck                          Pizza                          2
Deb                            Pizza                          1

(7 row(s) affected)

This is the basic group by you've already worked with, so nothing new here. Now, for new stuff. We want to make that Burgers and Pizzas, rather than the Thing and Qty.

Let's do Burgers first:
select a.[Name], 
    sum(case when a.[Thing]='Burger' then a.Qty else 0 end) as [Bugers]
  from (
    select [Name], [Thing], count(*) as Qty
      from #foo
      group by [Name], [Thing]
  ) a
  group by a.[Name]

Name                           Bugers
------------------------------ -----------
Alice                          3
Bob                            1
Chuck                          0
Deb                            2

(4 row(s) affected)

There are a few new concepts here. One is a subquery, or query in a query. Basically, our messy group by is wrapped in () and given the name a. You just treat the result set like a regular table or view: think anonymous view. The other thing is a case statement, which is how you get if then logic in SQL statements.

There is a third bit here, a pattern, when we use the case to return a value or zero if our criteria isn't met. We then use a sum to squish everything up into one line.

The next step, and your challenge, is to add another column for Pizzas.

Hope this helps.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1