asked on

'COUNT' does not work

I do have following tbl 'GFArchiv'

Symbol .... DatumBerechnung ..... Technik ....... Investitionsliste ..... Unterbewertung ...

2001 JP 10.11.2006 True True False
3541 JP 10.11.2006 True False True
...
2001 JP 11.11.2006 True True True
3541 JP 11.11.2006 True True True

I would like now to count how many at each day have been 'true' on Investitionsliste, but only the ones who had 'True" in Technik like:

Date CountTechnikTrue CountInvestitionslisteTrue
10.11.2006 2 1
11.11.2006 2 2

I've tried this with

SELECT CONVERT(smalldatetime, CONVERT(char(10), DatumBerechnung, 104), 104) AS Date, COUNT(Investitionsliste) AS Expr2
FROM dbo.GFArchiv
WHERE (Technik = 1)
GROUP BY CONVERT(smalldatetime, CONVERT(char(10), DatumBerechnung, 104), 104)
HAVING (COUNT(Investitionsliste) = 1)

but it runs forever. Seems something wrong but what?

Can I count in the same step also the one where 'Unterbewertung' is true, but only the ones where 'Investitionsliste' is true as well?

thx
Kongta

SOLUTION

Raynard7

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Otana

Try this:

SELECT
Date, SUM(CASE Technik WHEN 'True' THEN 1 ELSE 0 END) CountTechnikTrue, SUM(CASE Investitionsliste WHEN 'True' THEN 1 ELSE 0 END) CountInvestitionslisteTrue
FROM dbo.GFArchiv
WHERE Technik = 'True'
GROUP BY Date

HuyBD

SELECT CONVERT(smalldatetime, CONVERT(char(10), DatumBerechnung, 104), 104) AS Date,
SUM(Investitionsliste) AS Expr2
FROM dbo.GFArchiv
WHERE (Technik = 1)
GROUP BY CONVERT(smalldatetime, CONVERT(char(10), DatumBerechnung, 104), 104)

Kongta

ASKER

the fields 'Technik', 'Investitionsliste', 'Unterbewertung' are bit-fields and I get an error saying can't convert 'True'. I now changed true into 'CASE WHEN Investitionsliste= 1....

but now I don't get the error anymore but it runs again forever?

I inted to use the results for a diagram in MS Access front-end, shall I export data first in a table and use them after for the diagram or take the procedure as a source as it looks more complex than I thought?

imran_fast

try this

SELECT DatumBerechnung,
sum(case when Technik = 'true' then 1 else 0 end) countTechnik,
sum(case when Technik = 'true' and Investitionsliste = 'true' then 1 else 0 end) countInvestitionsliste,
FROM dbo.GFArchiv

GROUP BY DatumBerechnung

ASKER CERTIFIED SOLUTION

imran_fast

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kongta

ASKER

I use now imran_fast latest version but wait now 6'+ and QA is still running. Actualy there are 6 millions rst in the tbl, so I assume this should be no problem for SQL.

Kongta

ASKER

Sorry 8:27 and just got the results

imran_fast

hi Kongta,
Is it fine or have some issure in it.

Kongta

ASKER

I've now changed DatumBerechnung into CONVERT(smalldatetime, CONVERT(char(10), DatumBerechnung, 104), 104) but still takes very long. Do you see a way to improve? I have more complicated calculations which take not that long.

imran_fast

yeah create this index

Create index IX_GFArchiv on GFArchiv(DatumBerechnung)

regards

Imran

Kongta

ASKER

I had already an index (Symbol, Variante, DatumBerechnung) on this tbl. But DatumBerechnung ist like 10.11.2005 14:23:21. Shall I or can I convert this into 10.11.2005 meaning is there a easy way to convert all entries in the tbl at once and than proceed after with a more easy date?

imran_fast

>> there a easy way to convert all entries in the tbl at once and than proceed after with a more easy date?
updating 6 million rows will take hell of time.
try this if you dont succeed in speed up then add a new column in yourtable CustomDate update that column to have the value and then create index on that column
but first try the one below.

SELECT convert(varchar(10),DatumBerechnung, 102)
sum(case when Technik = 1 then 1 else 0 end) countTechnik,
sum(case when Technik = 1 and Investitionsliste = 1 then 1 else 0 end) countInvestitionsliste,
FROM dbo.GFArchiv

GROUP BY convert(varchar(10),DatumBerechnung, 102)

Kongta

ASKER

ok, doesn't improve that much. Let me try to solve by adding a new column.

I'll close this Q as the initial problem has been solved. Thx to all. I award Raynard7 & aneeshattingal with 100 points as their solution was correct as well & imran_fast with the 250 as he gave me the follow-up.

Rgds
Kongta