Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.
Course Of The Month
4 days, left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
'COUNT' does not work
Posted on 2006-11-13
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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
6
-
5
- +3
15 Comments
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
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
Active today
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)
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)
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
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
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)
SUM(Investitionsliste) AS Expr2
FROM dbo.GFArchiv
WHERE (Technik = 1)
GROUP BY CONVERT(smalldatetime, CONVERT(char(10), DatumBerechnung, 104), 104)
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?
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?
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
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
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
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
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.
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.
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?
>> 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),DatumB
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),DatumB
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),DatumB
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),DatumB
Featured Post
This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses
Course of the Month4 days, left to enroll
691 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.