COUNT question - 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

Transact-SQL (2000)

COUNT question

Author

Topic

nix
Starting Member

4 Posts

Posted - 2003-05-16 : 06:05:52

Hi,
how can i shrink this?

<%
SQL = "SELECT COUNT(*) AS elektronik_salj FROM kop_salj WHERE kategori = 'elektronik' AND typ = 'Säljes'"
rs.Open SQL, MinCon
elektronik_salj = rs("elektronik_salj")
rs.Close

SQL = "SELECT COUNT(*) AS elektronik_kop FROM kop_salj WHERE kategori = 'elektronik' AND typ = 'Köpes'"
rs.Open SQL, MinCon
elektronik_kop = rs("elektronik_kop")
rs.Close

SQL = "SELECT COUNT(*) AS fritid_salj FROM kop_salj WHERE kategori = 'fritid' AND typ = 'Säljes'"
rs.Open SQL, MinCon
fritid_salj = rs("fritid_salj")
rs.Close

SQL = "SELECT COUNT(*) AS fritid_kop FROM kop_salj WHERE kategori = 'fritid' AND typ = 'Köpes'"
rs.Open SQL, MinCon
fritid_kop = rs("fritid_kop")
rs.Close

SQL = "SELECT COUNT(*) AS konst_salj FROM kop_salj WHERE kategori = 'konst' AND typ = 'Säljes'"
rs.Open SQL, MinCon
konst_salj = rs("konst_salj")
rs.Close

SQL = "SELECT COUNT(*) AS konst_kop FROM kop_salj WHERE kategori = 'konst' AND typ = 'Köpes'"
rs.Open SQL, MinCon
konst_kop = rs("konst_kop")
rs.Close

SQL = "SELECT COUNT(*) AS ovrigt_salj FROM kop_salj WHERE kategori = 'ovrigt' AND typ = 'Säljes'"
rs.Open SQL, MinCon
ovrigt_salj = rs("ovrigt_salj")
rs.Close

SQL = "SELECT COUNT(*) AS ovrigt_kop FROM kop_salj WHERE kategori = 'ovrigt' AND typ = 'Köpes'"
rs.Open SQL, MinCon
ovrigt_kop = rs("ovrigt_kop")
rs.Close

MinCon.Close
%>

Regards,
Nix

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-05-16 : 07:20:04


select
    sum(case when kategori = 'elektronik' and typ = 'Säljes' then 1 else 0 end) as elektronik_salj,
    sum(case when kategori = 'elektronik' AND typ = 'Köpes' then 1 else 0 end) as elektronik_kop,
    sum(case when kategori = kategori = 'fritid' AND typ = 'Säljes' then 1 else 0 end) as fritid_salj,
    ....
from
    kop_salj

Assuming that by 'shrink' you mean return as one rowset ....

Jay White
{0}

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-16 : 07:54:06

Also -- try using a stored procedure instead of sending SQL text to the server .... that'll make your ASP script much smaller, your performance better and everything more secure.

- Jeff

nix
Starting Member

4 Posts

Posted - 2003-05-16 : 10:49:31

quote:

Also -- try using a stored procedure instead of sending SQL text to the server .... that'll make your ASP script much smaller, your performance better and everything more secure.

- Jeff

Can you explain that for me!
Tanks,
Nix

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources