SELECT DISTINCT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SELECT DISTINCT

Author

Topic

brmcdani441
Starting Member

7 Posts

Posted - 2011-10-14 : 15:03:41

The following query gives me the following result.

Code Type Total
143 INFORMATION 1
117 ENVIRONMENTAL 1
117 ENVIRONMENTAL 2
117 ENVIRONMENTAL 1
117 ENVIRONMENTAL 1

I Want Distinct values like this:
Code Type Total
143 INFORMATION 1
117 ENVIRONMENTAL 5

SELECT incident.rep_type_fk, report_type.rep_type, report_type.type_desc, incident.assigned_to_date, COUNT(*) AS countTotal, SUM(Count(*)) over() AS countTotalSum
 FROM   incident
 LEFT OUTER JOIN report_type ON incident.rep_type_fk=report_type.id_pk
 WHERE incident.assigned_to_date Between @BegDate AND @EndDate
 GROUP BY incident.rep_type_fk,report_type.rep_type,report_type.type_desc,incident.assigned_to_date
 ORDER BY incident.rep_type_fk

Thanks in advance!

craigwg
Posting Yak Master

154 Posts

Posted - 2011-10-14 : 16:39:09

Why doesn't this work:


SELECT DISTINCT incident.rep_type_fk, report_type.rep_type, report_type.type_desc, incident.assigned_to_date, COUNT(*) AS countTotal, SUM(Count(*)) over() AS countTotalSum
 FROM   incident
 LEFT OUTER JOIN report_type ON incident.rep_type_fk=report_type.id_pk
 WHERE incident.assigned_to_date Between @BegDate AND @EndDate
 GROUP BY incident.rep_type_fk,report_type.rep_type,report_type.type_desc,incident.assigned_to_date
 ORDER BY incident.rep_type_fk

Craig Greenwood

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 21:47:42

As per the output, you're having some additional columns in select which might be the cause.


SELECT incident.rep_type_fk, report_type.rep_type, report_type.type_desc, COUNT(*) AS countTotal, SUM(Count(*)) over() AS countTotalSum
 FROM   incident
 LEFT OUTER JOIN report_type ON incident.rep_type_fk=report_type.id_pk
 WHERE incident.assigned_to_date Between @BegDate AND @EndDate
 GROUP BY incident.rep_type_fk,report_type.rep_type,report_type.type_desc
 ORDER BY incident.rep_type_fk

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-10-15 : 05:43:51

You need to use Aggregate Sum function with group by clause of SQL on total column of your table.

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources