Can I accomplish this result with SQL?

[mrobb]

Hello,

I am trying to generate a SQL statement that is causing me some trouble. In fact, i'm not even sure if its possible to accomplish using SQL. Any help would be appreciated, even if you can point me in the direction of what to research.

-Matt

-----------------------------------------------------------

My database 'AUDITS' table looks like the following. Of course, the 'recommendation' field relates to another table.

ID NAME RECOMMENDATION (RECO)
-- ---- --------------
1 John 1
2 John 1
3 John 2
4 Jim 1
5 Jim 1
6 Sue 1

I would like to create a SQL query that returns a recordset as follows, showing the sums of the values from the same table:

NAME NO. OF RECORDS NO. OF (RECO)1 NO. OF (RECO)2
---- -------------- -------------- --------------
Jim 2 2 0
John 3 2 1
Sue 1 1 0

 

[jasonp45]

"Select Name, Count(Name) as Counts From Audits Group By Name Order By Name" should give you the first count. You ought to be able to join that up with a count from the second table, or, depending on how the recommendations field is structured (I'm not sure if it's a count above or some sort of foreign key), you may be able to generate all of your counts off the single table.

 

[SQLBill]

I haven't tested this, but I think it'll work....


select distinct name, count(name) as [no. of Records], (select count(recommendation(RECO)) from audits where recommendation(RECO) = 1) as [No. of (RECO)1], (select count(recommendation(RECO) from audits where recommendation(RECO) = 2) as [No. of (RECO)2]
from audits

-SQLBill

 

[mrobb]

I'm thinking I can accomplish it using temp tables within a stored procedure as well.

 

[bperry]

Temp tables are sometimes needed, but not in this case:

Select
Name,
COUNT(Name) as Name,
SUM(CASE when Reco = 1 then 1 else 0 END) as Reco1Cnt,
SUM(CASE when Reco = 2 then 1 else 0 END) as Reco2Cnt
From audits
Group By Name
Order by Name

-----------------
bperry

 

[bperry]

ooooops, how dumb of me.
I had the wrong alias name on the total count column:

Select
Name,
COUNT(Name) as RecCount,
SUM(CASE when Reco = 1 then 1 else 0 END) as Reco1Cnt,
SUM(CASE when Reco = 2 then 1 else 0 END) as Reco2Cnt
From audits
Group By Name
order by Name

 

[mrobb]

bperry:

Your method worked right away for me! Thanks much! I've never seen a CASE statement inside of SQL. Do you have any recommendations on books, websites, or links that have advanced SQL commands like that?

-Matt

 

[bperry]

The only book I have is Ken Henderson 'The Guru's Guide to T-SQL'. It's quite advanced, but get it if you want to learn the good stuff.

I am very crappy at recommending websites and links, but other forum readers looking at this thread will make some recommendations for you.

bperry