Help With Select Count Query - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Help With Select Count Query

Author

Topic

besadmin
Posting Yak Master

116 Posts

Posted - 2008-05-29 : 17:19:27

hi friends, any help is greatly appreciated!

My company is trying to create a Query...we are having trouble implementing a Select Count Function.

We have a 'type' column with fax, internet, phone, etc...in it.

We want to count the occurrence of each 'type' for each individual customer in the table.

so we have 1 row per customer displaying how many orders they have under each new column...fax, internet...etc.??

if you can help please let me know!

Thanks!

ps...here are some examples of possible pseudo code we have been trying a bit!

- Example Pseudo code
select strName,
(count(Itype) where iType = 'FAX') as 'FAX'),
(count(Itype) where iType = 'PHONE') as 'PHONE'),
(count(Itype) where iType = 'EDI') as 'EDI'),
(count(Itype) where iType = 'ESALES') as 'ESALES')
from #qwe
Group by strName, iType
Order by strName
for all distinct(iType)

drop table #qwe
go

select distinct(iType) from qwe

-- update qwe set iType = 'ESALES' where iType = '3'

-- insert into qwe values ('a', 'EDI')

SELECT 'FAX' = (select Count(iType) from #qwe where iType='FAX'),
'ESALES' = (select Count(iType) from #qwe where iType='ESALES'),
'EDI' = (select Count(iType) from #qwe where iType='EDI'),
'PHONE' = (select Count(iType) from #qwe where iType='PHONE')
FROM #qwe

Thanks again

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-29 : 17:27:23

SELECT SUM(CASE WHEN iType = 'FAX' THEN 1 ELSE 0 END ) AS FAX,
SUM(CASE WHEN iType = 'ESALES' THEN 1 ELSE 0 END) AS ESALES,
SUM(CASE WHEN iType = 'EDI' THEN 1 ELSE 0 END) AS EDI,
SUM(CASE WHEN iType = 'PHONE' THEN 1 ELSE 0 END) AS PHONE
FROM #qwe

E 12°55'05.25"
N 56°04'39.16"

nhaas
Yak Posting Veteran

90 Posts

Posted - 2008-05-29 : 17:28:16

This Might Help, I am working on the same thing - but now have a twist.
SELECT SERVICETYPE,COUNT(*)AS record_count FROM TABLE GROUP BY SERVICETYPE Order by SERVICETYPE

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-29 : 17:30:01

select strName, iType, count(*)
from #qwe
Group by strName, iType
with rollup
Order by strName

E 12°55'05.25"
N 56°04'39.16"

besadmin
Posting Yak Master

116 Posts

Posted - 2008-05-30 : 13:06:57

Wow, thanks a ton everyone for quick replys!

i will implement these solutions and report back which ones worked most satisfactorily.

thanks again

-S

besadmin
Posting Yak Master

116 Posts

Posted - 2008-05-30 : 14:57:10

The First reply from peso is the winner.

Thanks a ton to everyone who responded though! although the other queries weren't exactly what i was looking for, they still produced some cool results which make for very useful queries to know.

--Anyway,

I have another question now concerning the same table and query.

i have a select (some stuff on a table}

so then...how can i add this new select query to the other one?
so the first one acts on the original table, and this new one acts on those results? if you know what i mean.

again, any help is much appreciated

thanks friends!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources