|
Hello,
I'm having a little problem with the COUNT function. I have a table with payments for customers (say 42 or 54 each), and each payment is marked as paid whenever the customer comes and pays it.
I want to make a query that gives me all the customers with the number of paid payments (including those customers new for this week). The total number of customers is 328, but if I add the COUNT(*) the resulting table only contains 291, all with 1 or more payments (meaning 37 customers have 0 payments.)
Looking in the web I found that COUNT(column) returns the number of rows in which that column is not NULL. In this case, I'm using COUNT(*) which should return the count of all rows. I'm also using a WHERE clause to limit the counted payments to only those that have been paid. I tried a LEFT JOIN, so that at least the resultset would include the rows, even if the count column is NULL, but it didn't work.
Any ideas?
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
|
Not exactly the answer I needed, but that'll help too.
I think my problem was that I was counting all the records from a resultset matching a criteria instead of counting some records matching a criteria from resultset from all records. I split the query into embedded subqueries and the results came as expected, except I was getting NULL instead of zeros. Your suggestion fixed it.
Thanks!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
do you group by the right columns ?
how is your select written please ?
TOXCCT >>> GEII power
[toxcct][VisualCalc]
|
|
|
|
|
|
Hi,
Is there a reason why sometimes some of the link options in Crystal Reports are disabled?
eg the FULL OUTER JOIN.
I can't think of a reason why a full outer join wouldn't work?
Hope somebody can help me.
tnx!
No hurries, no worries.
|
|
|
|
|
Hi,
I found that there are two practices for writing queries for adapter.
1. one adapter queries for one table in the database
e.g. select * from customer
2. one adapter queries for joinned table in the database
e.g. select * from customer c1, country c2 where c1.country = c2.country
Are the update/insert/delete command of an adapter work only for one table only?
Which practice is encouraged?
If the two tables are filled into the dataset, are there any SQL queries method to do the "join" operation to the two tables? anymore operations can be done?
Thanks
|
|
|
|
|
scchan1984 wrote:
Are the update/insert/delete command of an adapter work only for one table only?
Personally, I would never rely on the auto generated code for the INSERT/UPDATE/DELETE commands in an Adapter - because it cannot assume anything about the database it creates some awful SQL to ensure that it works.
scchan1984 wrote:
Which practice is encouraged?
I would encourage you not to use any Wizard generated adapters. Also, never do a SELECT * if the underlying table changes then the results back to your application may be different and your application may not expect that.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Is there a way to package and deploy only the engine and any runtime support for a SQL Server database?
If not and I have to tell the user that SQL Server is required, what is the easiest way to install my application's schema and data into an existing SQL Server database? In previous situations, I've exported the data and used the bcp command-line app to import the data. Is that the best way or is there an alternative?
Cheers,
Tom Archer - Archer Consulting Group
"So look up ahead at times to come, despair is not for us. We have a world and more to see, while this remains behind." - James N. Rowe
|
|
|
|
|
|
I have a DataTable (a table in ASP.NET) in memory which I need to pass into an SQL stored procedure.
How can I achieve this? Is there any to pass the contents of the table into my stored procedure given that the size (number of rows) of the table is unknown?
|
|
|
|
|
Convert it to an XML document and use OpenXML in your stored procedure to turn it into a table.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
Thanks for the response.
At the moment, I've just done it as a string with delimiters (commas and full-stops). Is there any disadvantages of doing it this way? Are there any advantages of using an XML document?
|
|
|
|
|
The advantages are just that the documents form is in a sensible format, that is to say, the XML parser is able to tell what's data and what's metadata. If you pass in a comma or full stop in your data, you could have problems.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
HI
I WANT TO GATHER ALL INFORMATION @ TABLENAMES, PARMETER, PARAMETER VALUE,
CHILD PROCEDURES OF STORED PROCEDURES WHEN THAT SP CALL IN APLLICATION
|
|
|
|
|
|
I have create a diagram is between two table name "Truck" and "Contractor"
Primary Key table is contractor - ID
Foreign Key table is Truck -contractorID
I have check the check box(check the existing data on creation and Enforce the relationship for replication)
And uncheck the check box(Enforce the relationship for update and delete)
But when i go to my "Contractor" Table
I still can delete my data....
What i want is ....
I cannot Delete a record from the primary-key table if there is and matching foreign-key record.
What i should do to control it .....
Cheer Up (^_^)
|
|
|
|
|
Hi all!
Please help. I have a table in MS SQL database say persons with information of persons stored in one row, now I want the information of all the persons in different row:
p1_dob P1_age P1_sex p2_dob P2_age P2_sex p3_dob P3_age P3_sex
2000 05 M 1974 31 F 2001 04 M
to be:
p dob age sex
1 2000 05 M
2 1974 31 F
3 2001 04 M
______________
Please write me an sql stored procedure to transpose that kind of data.
Thank you.
phokojoe
|
|
|
|
|
|
Hi Colin
Thank you, it has worked. But one more question, What must I write so that I insert the newly transposed table contents into a new table.
phokojoe
|
|
|
|
|
|
Hi all!
Please help. I have a table in MS SQL database say persons with information of persons stored in one row, now I want the information of all the persons in different row:
p1_dob P1_age P1_sex p2_dob P2_age P2_sex p3_dob P3_age P3_sex
2000 05 M 1974 31 F 2001 04 M
to be:
p dob age sex
1 2000 05 M
2 1974 31 F
2 2001 04 M
______________
Please write me an sql stored procedure to transpose that kind of data.
Thank you.
phokojoe
|
|
|
|
|
I try to open een mssql database using using visual web.
When i import a table to a page for viewing and then running the page in a browser i get a parser error:
Parser Error Message: Unrecognized configuration section 'connectionStrings'
And the partrt of web.config that visual web writes looks like this:
Line 1: <configuration>
Line 2: <connectionstrings>
Line 3: <add name="KiddinSysConnectionString1" connectionstring="Data Source=EIBERGEN01;Initial Catalog=KiddinSys;Integrated Security=True"
line="" 4:="" providername="System.Data.SqlClient">
And nomatter what i do i cant get it wright and working. Is there something i forgot to decleare but i think that visualweb schould ha\ndle this.
Thanks for any help
Harry
|
|
|
|
|
gharryh wrote:
Unrecognized configuration section 'connectionStrings'
gharryh wrote:
providerName="System.Data.SqlClient" />
Did you forget to check 'Do not treat <'s as HTML tags' ???
Either way, it seems your config file is wrong. Start with a hard coded connection string, then when that works, read up on how to add stuff to your config file in MSDN, and do that seperately. Then you can solve one problem at a time.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
Hi,
I am a newbie to asp.net and havo to write a simple webpage to connect to a mssql database on a iis machine. I am used to wrok with php and mysql and having no problems of connecting to a database in that enviroment, but cant get it wright in the mssql and iis (aspx).
Any simple help with html code would be welcome.
Harry
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
