This is a WPF application that is backed up a local SQLite database. I am parsing a couple hundred text files at to pull error data.
I am new to SQL am trying to build a query to count the total occurrence of error codes as well as the sub total by each ‘Tool Id’
The Error Codes and Tool Ids all input from the text files and are not known at runtime. I am currently putting the query results into a WPF datatable to display.
Here is what my database looks like.
| Db Id | Lot Id | Tool Id | Error Code | Error Title |
|---|---|---|---|---|
| 1 | B410D9Y0 | CNP501 | 5000-503-500-BB8 | Measurement error |
| 2 | B411DANA | CNP501 | 5000-503-500-BB8 | Measurement error |
| 3 | B414DF40 | CNP502 | 5000-503-500-BB8 | Measurement error |
| 4 | B414DF40 | CNP502 | 5000-503-500-BB8 | Measurement error |
| 5 | B414DF40 | CNP502 | 5000-503-500-BB8 | Measurement error |
| 6 | B414DFW0 | CNP502 | 1000-320-3FC-3F7 | Main sequence |
| 7 | B414DFW0 | CNP502 | 1000-0-4-0 | Sequence Paused by Error |
| 8 | B414DFW0 | CNP502 | 5000-503-500-BB8 | Measurement error |
| 9 | B414DFW0 | CNP502 | 5000-503-500-BB8 | Measurement error |
| 10 | B414DFW0 | CNP502 | 5000-503-500-BB8 | Measurement error |
| 11 | B414DFW0 | CNP502 | 5000-503-500-BB8 | Measurement error |
I have queries to get the individual pieces.
SELECT [Error Code], COUNT(*) AS Total
FROM ErrorEntries
GROUP BY [Error Code]
This gives me the error code totals
| Error Code | Total |
|---|---|
| 1000-0-4-0 | 1 |
| 1000-320-3FC-3F7 | 1 |
| 5000-503-500-BB8 | 9 |
SELECT [Tool Id], [Error Code], COUNT(*) AS Total
FROM ErrorEntries
GROUP BY [Tool Id], [Error Code]
This gives me the totals split by Tool Id
| Tool Id | Error Code | Total |
|---|---|---|
| CNP501 | 5000-503-500-BB8 | 2 |
| CNP502 | 1000-0-4-0 | 1 |
| CNP502 | 1000-320-3FC-3F7 | 1 |
| CNP502 | 5000-503-500-BB8 | 7 |
But I need them combined so the tool totals are displayed next to the error code totals
| Error Code | Total | CNP501 | CNP502 |
|---|---|---|---|
| 1000-0-4-0 | 1 | 0 | 1 |
| 1000-320-3FC-3F7 | 1 | 0 | 1 |
| 5000-503-500-BB8 | 9 | 2 | 7 |
Jordan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1