Solved

'COUNT' does not work

Posted on 2006-11-13
15
313 Views
Last Modified: 2008-02-26
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



0
Comment
Question by:Kongta
15 Comments
 
LVL 35

Assisted Solution

by:Raynard7
Raynard7 earned 100 total points
ID: 17928585
Hi,

The query that you supplied did not give both counts.

If you did

SELECT     CONVERT(smalldatetime, CONVERT(char(10), DatumBerechnung, 104), 104) AS Date, sum(case when Investitionsliste = true then 1 else 0  end) AS countInvestitonsliste, sum(case when Technik = true then 1 else 0  end) AS countTechnik,
FROM         dbo.GFArchiv
WHERE     (Technik = 1)
GROUP BY CONVERT(smalldatetime, CONVERT(char(10), DatumBerechnung, 104), 104)
HAVING      (COUNT(Investitionsliste) = 1)


it should give you the count of each
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 100 total points
ID: 17928586
SELECT     CONVERT(smalldatetime, CONVERT(char(10), DatumBerechnung, 104), 104) AS Date,
                SUM( CASE WHEN  Investitionsliste= 'True' THEN 1 ELSE 0 END) AS Expr2,
                SUM( CASE WHEN  Unterbewertung= 'True' THEN 1 ELSE 0 END) AS Expr3

FROM         dbo.GFArchiv
WHERE     (Technik = 1)
GROUP BY CONVERT(smalldatetime, CONVERT(char(10), DatumBerechnung, 104), 104)
0
 
LVL 11

Expert Comment

by:Otana
ID: 17928591
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
0
 
LVL 17

Expert Comment

by:HuyBD
ID: 17928594
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)
0
 

Author Comment

by:Kongta
ID: 17928644
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?
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17928648
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
0
 
LVL 28

Accepted Solution

by:
imran_fast earned 250 total points
ID: 17928650
SELECT      DatumBerechnung,
          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  DatumBerechnung
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:Kongta
ID: 17928689
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.
0
 

Author Comment

by:Kongta
ID: 17928692
Sorry 8:27 and just got the results
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17928703
hi Kongta,
Is it fine or have some issure in it.
0
 

Author Comment

by:Kongta
ID: 17928711
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.
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17928739
yeah create this index

Create index IX_GFArchiv on GFArchiv(DatumBerechnung)

regards

Imran
0
 

Author Comment

by:Kongta
ID: 17928755
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?
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17928785
>> 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)
0
 

Author Comment

by:Kongta
ID: 17928824
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
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Union 20 44
Get Duration of last Status Update 4 29
SQL server is using more virtual memory. 5 63
Help Extract Specific in SQL 8 15
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now