SQL Syntax multiple columns Count and Group by

Let me try to explain this to the best of my ability.  I am running a php page that connects to a SQL server database.  The following syntax works for part 1 of what I want to do.

$sql = "SELECT LTRIM(Rtrim(A1.CompanyName)) As CompanyName,
LTRIM(Rtrim(A1.FileDesc)) As FileDesc,
Count(A1.FileDesc) As TotalCount
FROM Computer A0 (nolock) LEFT OUTER JOIN FileInfoInstance A1 (nolock) ON A0.Computer_Idn = A1.Computer_Idn
WHERE A1.FileDesc != ''
AND A1.FileDesc NOT LIKE 'Microsoft%'
Group By A1.CompanyName, A1.FileDesc
ORDER BY  A1.CompanyName";

This produces results of Company Name, File Description and total count of file descriptions.  Each row represents what I need.

BUT - I am trying to produce a result set ROW of CompName, FileDesc and Count of File Desc (which I am) but right underneath it I would like to list EACH Computer_Idn, DeviceName and FullName from A1 FileInfoInstance.

My issue is when I include a select statement in my main query above that includes A1.Computer_Idn, I cannot group by FileDesc.  Even if I dont include it in my output.

So, in plain text I want to select CompanyName, File Description, Computer_Idn and Count of File Descriptions but not show the Computer_Idn's on this row,  In my "while" loop, after each row, I will then use my selected Computer_Idn for that row to run another query to output DeviceName and FullName.

Any ideas?

Microsoft - Word - 3
idn1 - 12345 - John Doe
idn2 - 12346 - Sally Smith
idn3 - 12347 - Jane Doe

Corel - WordPerfect - 2
idn1 - 12345 - John Doe
idn3 - 12347 - Jane Doe
dcjsdtsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SharathData EngineerCommented:
What do you mean by underneath? The query gives the result in tabular format. Do you want the result set like this?

Microsoft - Word - 3 idn1 - 12345 - John Doe
Microsoft - Word - 3 idn2 - 12346 - Sally Smith
Microsoft - Word - 3 idn3 - 12347 - Jane Doe
0
dcjsdtsAuthor Commented:
The first query will run as-is and product the results as follows:

Microsoft - Word - 3
Corel - WordPerfect - 2

As my query loops through, my PHP code builds the output.

while(odbc_fetch_row($result))
{ 
  
  $pCompName=odbc_result($result,1); 
  $pFileDesc=odbc_result($result,2);
  $pCount=odbc_result($result,3);

$pCompName = htmlspecialchars(stripslashes($pCompName));
$pFileDesc = htmlspecialchars(stripslashes($pFileDesc));
  
//format and display results
print ("<tr bgcolor='$row_color'>");
print ("<td> $pCompName </td>");
print ("<td> $pFileDesc </td>");
print ("<td> $pCount </td>");
print ("</tr>\n");

}

Open in new window


So here : I will be executing another query that will use Computer_Idn to identify ID, FullName and Device name.  The results of this query will then output a new <TR> and <TD> containing those records.

$sql2 = "SELECT LTRIM(Rtrim(A0.DeviceName)) As DeviceName,
LTRIM(Rtrim(A0.FullName)) As FullName
FROM Computer A0 (nolock) LEFT OUTER JOIN FileInfoInstance A1 (nolock) ON A0.Computer_Idn = A1.Computer_Idn
WHERE A1.Computer_Idn = $myIDNVariable from the abve row output.  The problem is If I add Computer_Idn to my first query I am unable to group by Group By A1.CompanyName, A1.FileDesc.

So, what I am looking to do is capture the Computer_Idn output field as a PHP variable and use it as my criteria for my subquery and sub-form (so to speak)

Unless their is an easier way.  I prefer to do it this way because my actual result set contains over 40,500 records.  I would rather have:

Company - Product - Qty
Corel  - WordPerfect - 300 (actual real estimated qty)
Then right beneath this line in a new table row with a much smaller line height, I can list all 300 users and devices with this product installed.
0
dcjsdtsAuthor Commented:
I agree, but I consider myself a 6 on a 1 - 10 scale of PHP.   I do understand that you understand my point and I think I am going about the SQL queries the wrong way.  I will need to run 2 queries and also let PHP (which I can do) to do my counts and results display.

I just wanted to be able to SQL query as efficiently as possible - meaning NOT return all quadbillion records (for each Computer_Idn) and then filter through them.  As it turns out, I do.  I will use PHP to opt to display those repetative records or not.  That is probably the best way.

BUT since this is still unresolved, I will post another question in a different way, on a different topic.
0
Kevin CrossChief Technology OfficerCommented:
I see acperkins is helping you with some query ideas, so you should be in good hands there. On the overall implementation, I would suggest going with something efficient and ultimately along the lines of what http:#37348239 is suggesting. In other words, you would get a denormalized resultset (i.e., one result set that represents the individual row information with the header data repeated. You would sort by the header information.

In PHP, you would loop through the results, storing the last displayed header. If the current header is different from the last header, you would create a new heading in whatever HTML format you want and print the detail for that line. On subsequent lines where the current header matches the last one, you would skip displaying header and just display that line's details.

So your initial query is bigger, but you make less trips back and forth to the database and so potentially  make the overall process more efficient, especially once you optimize the query through efforts in the other question.

Hope that helps!

Best regards,

Kevin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dcjsdtsAuthor Commented:
I agree with this concept.  I can run the large query one time and use the design of PHP to display what I want.   if($prodname != $previousProdName){do this} else{print that).  Also for each "while loop" I can increment my counter and not even use the Count in SQL.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.